[prev in list] [next in list] [prev in thread] [next in thread] 

List:       solid
Subject:    Re: Index Usage
From:       "Timo Hotti" <timo.hotti () solidtech ! com>
Date:       2000-02-07 21:28:36
[Download RAW message or body]

Ivor,

On 2/7/2000 7:49:30 AM, Ivor Flannery  wrote:
>We are running Solid 3 under Linux and have a table with approx. 5 million
>rows in it.  The table has no primary key, but has a number of indexes
>built.  An index named idx_sti_date exists on the 'call_date' column.  The
>call_date columns is a type date.
Putting the CALL_DATE to be the first column of the primary key might do
the trick here. The primary key is an index that actually has all the data
of the table, sorted physically by the key order. Primary key is the
fastest possible way to access data of the table. Secondary indices are
significantly slower because they always require two lookups to the
database: one for the index and one for the data.

In general, user-defined primary keys are a Good Thing.

>When I try to get an 'explain plan' I get two very different answers
>depending on whether I give Solid a single date or a date range.
>
>explain plan for SELECT user_id,charge,call_date FROM STI_CDR WHERE
>call_date = '2000-01-01'
>
>..... JOIN
>..... TABLE           STI_CDR
>                      INDEX IDX_STI_DATE
>                      CALL_DATE = 2000-01-01
>
>explain plan for SELECT user_type,charge,call_date FROM STI_CDR WHERE
>call_date between '2000-01-01' and '2000-01-31'
>
>..... JOIN
>..... TABLE           STI_CDR
>.....                 SCAN TABLE
>.....                 CALL_DATE <= 2000-01-31
>.....                 CALL_DATE >= 2000-01-01
>
>As you can imagine, the scan table takes forever.  What I don't understand
>is why Solid does not at least just use the idx_sti_date index instead of
>an entire table scan. Can anyone give me an idea of what I am doing wrong
>here.
The optimizer probably estimates here that scanning the table is faster
than looking up large number of separate rows via the index.

Regards,

Timo

[prev in list] [next in list] [prev in thread] [next in thread]