where clause and index question

classic Classic list List threaded Threaded
2 messages Options
Reply | Threaded
Open this post in threaded view
|

where clause and index question

Stanislaw Stepien
Hi

I have found following in 'The SQLite Query Optimizer Overview':

 It is not necessary for every column of an index to appear in a WHERE
 clause term in order for that index to be used. But there can not be
 gaps in the columns of the index that are used. Thus for the example
 index above, if there is no WHERE clause term that constraints column
 c, then terms that constraint columns a and b can be used with the
 index but not terms that constraint columns d through z.

Why does it works that way ? Why it is not possible to implement
optimiser to use 'd' column index ? Why can't it  take 'd' index column
to speed up the query.
Now Sqlite user need to worry about optimisation steps and I think
this kind of problem has a simple solution. There are many questions
regarding index problems.

Best regards,
Stan
Reply | Threaded
Open this post in threaded view
|

Re: where clause and index question

Martin Engelschalk
Hello Stanislaw,

i think you may be mistaken. Indices in sqlite are implemented as trees.
If the where clause does not include column c, there is no way to use
the tree beyond the column b. The program has to look at every row that
meets the clauses involving a and b.

Also, in my experience, other databases also work like that. And a
programmer always has to keep an eye on her/his queries and check if the
database can execute them fast enough (or create additional indices).

Martin

Stanislaw Stepien schrieb:

>Hi
>
>I have found following in 'The SQLite Query Optimizer Overview':
>
> It is not necessary for every column of an index to appear in a WHERE
> clause term in order for that index to be used. But there can not be
> gaps in the columns of the index that are used. Thus for the example
> index above, if there is no WHERE clause term that constraints column
> c, then terms that constraint columns a and b can be used with the
> index but not terms that constraint columns d through z.
>
>Why does it works that way ? Why it is not possible to implement
>optimiser to use 'd' column index ? Why can't it  take 'd' index column
>to speed up the query.
>Now Sqlite user need to worry about optimisation steps and I think
>this kind of problem has a simple solution. There are many questions
>regarding index problems.
>
>Best regards,
>Stan
>  
>