I know that I can use the ANALYZE command or that I can index both
columns.
I was making some tests and I found that with one index the query is
slower that without any index, so I just trying to understand the
reason... I do not want to run it faster, I already know that it is
possible.
Database is uniformly distributed, I created it ad hoc just for my
test (sqlite 3.3.12):
CREATE TABLE table1 (a INTEGER, b INTEGER)
150,000 rows are inserted with:
INSERT INTO table1 (a,b) VALUES (5,10)
200 rows are inserted with:
INSERT INTO table1 (a,b) VALUES (5,11)
150,000 rows are inserted with:
INSERT INTO table1 (a,b) VALUES (4,11)
And the query was:
SELECT * FROM table1 WHERE a=5 AND b=11;
New benchmarks:
WITHOUT INDEX: 0.281 secs
WITH TWO INDEXes: 0.463 secs
WITH TWO INDEXes and the ANALYZE command: 0.480 secs
INDEXes are:
CREATE INDEX index1 ON table1(a);
CREATE INDEX index2 ON table1(b);
---
Marco Bambini
On Apr 23, 2007, at 9:36 PM, [EMAIL PROTECTED] wrote:
Marco Bambini <[EMAIL PROTECTED]> wrote:
Yes, I know that it is faster ... I just wonder why with one index
the query is slower that without any index...
Probably because most of the entries in your table
match the term being indexed. In your case, this
likely means that a large fraction of the table
entries have a=5.
When searching from an index, SQLite first finds
the index entry, then has to do a binary search
for the table entry. The table entry lookup
is O(logN) where N is the number of entries in
the table. If the number of rows in the result
set is proportional to N, then the total runtime
is O(NlogN). On the other hand, the total runtime
of a full table scan (which is what happens if you
omit the index) is O(N). N<NlogN.
Usually when you index something, you expect only
a constant number of entries in the table with that
value. In that case, an indexed lookup is O(logN).
A full table scan is still O(N). logN<N. So
usually an indexed search works out better. At least
that is the assumption that the SQLite optimizer
makes in the absence of information to the contrary.
Running ANALYZE gives the optimizer more information
about the index contents. It might choose a full
table scan over an indexed lookup in your case.
Have you tried it?
--
D. Richard Hipp <[EMAIL PROTECTED]>
----------------------------------------------------------------------
-------
To unsubscribe, send email to [EMAIL PROTECTED]
----------------------------------------------------------------------
-------
-----------------------------------------------------------------------------
To unsubscribe, send email to [EMAIL PROTECTED]
-----------------------------------------------------------------------------
