Hi Richard,
It's great to see clarity on this "from the horse's mouth" (I hope
that translates across the equator). Thank you.
- 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.
That's great! :-) That's much faster and more versatile.
My source seems incorrect, or perhaps my understanding of it. Where is
it, ah yes, this book here, flipping pages... In the book "The
Definitive Guide to SQLite", chapter 4, page 155, under the title
"Indexes", it reads:
If you have a table of 10,000 records, a sequential scan will read
all 10,000 rows to find all matches, while an index scan will read
4 rows (log(10,000)) to find the first match (and from that point
on it would be linear time to find all subsequent matches).
Is that last part wrong (about linear time for subsequent matches), or
am I misinterpreting it?
2. From left to right in the same order as your index... 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.
Again, that's good to know. Again I've located the source of my info
on the next page of the same book, which says:
Index Utilization
...
Say you have a table defined as follows:
CREATE TABLE foo (a,b,c,d);
Furthermore, you create a multicolumn index as follows:
CREATE INDEX foo_idx on foo (a,b,c,d);
The columns of foo_idx can only be used sequentially from left to
right. That is, in the query SELECT * FROM foo WHERE a=1 AND b=2
AND d=3 only the first and second conditions will use the index.
The reason the third condition was excluded is because there was
no condition that used c to bridge the gap to d. Basically, when
SQLite uses a multicolumn index, it works from left to right
column-wise. It starts with the left column and looks for a
condition using that column. It moves to the second column, and
so on. It continues until either it fails to find a valid
condition in the WHERE clause that uses it or there are no more
columns in the index to use.
Is that wrong, or am I just not getting it?
3. ... The last test (only) may be one or two inequality tests,
such as ">" or "<".
Each index can use at most one inequality operator and it must be on
the right-most column of the index that gets used.
"The book" seems to indicate that we can use at most two (not one)
inequality operators, where it says:
SQLite will use a multicolumn index only if all of the conditions
use either the equality (=) or IN operator for all index columns
except for the right-most index column. For that column, you can
specify up to two inequalities to define its upper and lower
bounds.
Or am I misunderstanding?
Thanks for your patience with any obvious errors I may be making.
Tom
-----------------------------------------------------------------------------
To unsubscribe, send email to [EMAIL PROTECTED]
-----------------------------------------------------------------------------
