Hi Tom,

Thanks for that; useful to know.
Didn't know about point 1 and 2 and that will complicate matters a bit
further.

RBS

-----Original Message-----
From: T&B [mailto:[EMAIL PROTECTED] 
Sent: 31 July 2007 00:39
To: sqlite-users@sqlite.org
Subject: Re: [sqlite] strategy adding indexes

Hi RBS,

> - indexes that include all possible combinations of fields that may  
> appear
> in a WHERE clause.

As an aside, note that, AFAIK, indexes are only used:

1. To get the first match of a query. If you ask for more than one  
matching record, the second, third etc matches are found by searching,  
not through the index.

2. From left to right in the same order as your index. So if you  
create index MyIndex on MyTable ( Column1, Column2, Column3 ), then  
you must test them in the same order, eg: where Column1 = Value1 and  
Column2 = Value2 or Column3 = Value3. If you miss a column in the  
sequence or place one out of order, the index won't be used from that  
point in the test onwards.

3. In equality tests, eg "=" (equals) and "in". If you use "like" for  
comparison, the index isn't used. The last test (only) may be one or  
two inequality tests, such as ">" or "<". And that last test must be  
in sequence (ie rule 2).

I hope this helps a bit. Some more learned SQLiters out there may care  
to correct or clarify.

Tom


----------------------------------------------------------------------------
-
To unsubscribe, send email to [EMAIL PROTECTED]
----------------------------------------------------------------------------
-




-----------------------------------------------------------------------------
To unsubscribe, send email to [EMAIL PROTECTED]
-----------------------------------------------------------------------------

Reply via email to