Michael Hunley wrote:
At 10:37 AM 1/14/2004 -0500, D. Richard Hipp wrote:
In some cases you can avoid the O(logN) lookup of the
main table entry and just use the index. For example:
SELECT count(*) FROM table WHERE col1>'abc' AND col1<'xyz';
Wasn't that the original question, Ken? Except it was a count(*) on a
JOIN.
Dr Hipp, would the same optimization apply if it is stepping through two
indices? In which case Ken should see a huge speed improvement to his
original question by adding an index and updating to the latest SQLite
(after you release 2.8.11, that is ;).
In the original query, the result was indeed a count(*) so no
access to the data we required there. But access to the data
was required in order to evaluate the WHERE clause. So it is
still O(NlogN).
But it is also still really fast. Do this: Run the same query
on SQLite, PostgreSQL, MySQL, and Oracle and see which one
finishes first. I've not tried it so I don't know what will
happen. But I'm guessing SQLite will be the clear winner.
Somebody please correct me if my guess is wrong.
--
D. Richard Hipp -- [EMAIL PROTECTED] -- 704.948.4565
---------------------------------------------------------------------
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
