Re: [sqlite] strategy adding indexes
drh Tue, 31 Jul 2007 03:12:54 -0700 T&B <[EMAIL PROTECTED]> wrote: > 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. No. If an index is used to retrieve the first row in the result, then it continues to be used for every row. Depending on the query, it is often possible to not have to do a full binary search of the index on the second and subsequent rows. Instead, the next matching index entry might be adjacent to the previous one so finding the next value might be as simple as moving one index entry to the left or to the right. But whether or not you can do this depends on the query. And the index is still used for every row, regardless. > > 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. The order of terms in a WHERE clause make no difference. All of the following work the same: WHERE column1=1 AND column2=2 AND column3=3 WHERE 2=column2 AND column3=3 AND column1=1 WHERE column2=2 AND 1=column1 AND column3=3 WHERE 3=column3 AND 2=column2 and 1=column1 WHERE column3=3 AND column1=1 AND 2=column2 WHERE 1=column2 AND column3=3 AND column2=2 Notice also that A=B is the same as B=A. But it is the case that you must cover some prefix of terms from the index. So if the index is on column1,column2,column3 in that order, and if you say: WHERE column1=1 AND column2!=7 AND column3=3 Then only the first term (column1) will be used by the index because you cannot use an index on a != operator. If you say WHERE column1!=7 AND column2=2 AND column3=3 Then the index cannot be used at all. If you say: WHERE column1=1 AND column2>3 AND column3=3 Then the first two terms of the index will be used. Each index can use at most one inequality operator and it must be on the right-most column of the index that gets used. > > 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). > http://www.sqite.org/optoverview.html -- D. Richard Hipp <[EMAIL PROTECTED]> Thanks, that is very useful. I wonder now if there would any mileage in writing an application (or does it exist already?) that takes a table (or tables) and all the possible queries and from those 2 produces a sensible plan to add the indexes. I suppose to do that you need a good understanding of the SQLite optimizer and if you have that then maybe you don't need such an application. Just one thing I noticed: When I run a query like for example this: Select from tableX where read_code GLOB 'bd*' and entry_type = 8 It takes about 2 minutes Now when after that I run this query: Select from tableX where read_code GLOB 'bx*' and entry_type = 8 It will run in 10 seconds. Why is this if the second query is not the same as the first? RBS ----------------------------------------------------------------------------- To unsubscribe, send email to [EMAIL PROTECTED] -----------------------------------------------------------------------------
