Federico Granata wrote:
Hi, I'm testing index on sqlite ver 3.0.7.
I've create a really simple database
CREATE TABLE tbl1(rowid integer primary key,fld1 text,fld2 integer);
and fill it with 1000000 row like this
insert into tbl1(fld1,fld2) values("blablablablablablabla",759928);
Then I copy this test.db to test-index.db and add a index (on test-index.db)
CREATE INDEX ind1 on tbl1(fld2);
time sqlite3 test.db "select * from tbl1 where fld2>12345;" > /dev/null
real 0m21.452s
user 0m20.503s
sys 0m0.809s
and
time sqlite3 test-index.db "select * from tbl1 where fld2>12345;" > /dev/null
real 1m15.840s
user 0m54.799s
sys 0m16.131s
The fld2>12345 terms does not restrict the result very much - probably
most of your 1000000 records met this condition. So it is faster to
do a full table scan and discard the unwanted records (which is what the
first example does) rather than look up each record that has a value
of fld2>12345 using the index. A full table scan can do a linear search
through the database. An index lookup does a linear search through the
index, but for each index entry it has to do an O(NlogN) lookup of the
corresponding table entry.
A larger and more complex database engine might recognize this fact
and decide not to use the index in the second example. SQLite is more
simple minded. It always uses an index if one is available. You can
cause it to not use an index by changing the WHERE clause slightly,
to something like this perhaps:
SELECT * FROM tbl1 WHERE fld2-12345>0;
Note also that the query results are different. The first query above
outputs rows in ROWID order. The second query outputs rows in FLD2
order.
--
D. Richard Hipp -- [EMAIL PROTECTED] -- 704.948.4565
