Marco Bambini wrote:
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,
You have a kind of pathological case here. Your data is not uniformly
distributed. You have only three kinds of records. 150,200 records match
the index on a for a = 5, and 150,200 records match the index on b for
b=11. So it doesn't matter which single index sqlite chooses, it will
have to scan through 150,200 records comparing the un-indexed field's
value. To do this it must extract the rowid from the index and then
locate that row in the main table, and then compare the value of the
un-indexed field.
Scanning an index for 150,200 records and then looking up 150,200
records in the main table is simply more work than scanning the entire
table of 300,000 records once.
This case does not benefit from indexing, and in fact it is slowed down
on both lookups and inserts.
Dennis Cote
-----------------------------------------------------------------------------
To unsubscribe, send email to [EMAIL PROTECTED]
-----------------------------------------------------------------------------
