I found some kind of workaround to solve this problem. Create new database and run:
CREATE TABLE foo(bar); INSERT INTO foo VALUES(null); INSERT INTO foo VALUES(null); INSERT INTO foo VALUES(null); BEGIN; INSERT INTO foo SELECT bar FROM foo; INSERT INTO foo SELECT bar FROM foo; INSERT INTO foo SELECT bar FROM foo; INSERT INTO foo SELECT bar FROM foo; INSERT INTO foo SELECT bar FROM foo; INSERT INTO foo SELECT bar FROM foo; INSERT INTO foo SELECT bar FROM foo; INSERT INTO foo SELECT bar FROM foo; INSERT INTO foo SELECT bar FROM foo; INSERT INTO foo SELECT bar FROM foo; INSERT INTO foo SELECT bar FROM foo; INSERT INTO foo SELECT bar FROM foo; INSERT INTO foo SELECT bar FROM foo; INSERT INTO foo SELECT bar FROM foo; INSERT INTO foo SELECT bar FROM foo; INSERT INTO foo SELECT bar FROM foo; INSERT INTO foo SELECT bar FROM foo; INSERT INTO foo SELECT bar FROM foo; INSERT INTO foo SELECT bar FROM foo; INSERT INTO foo SELECT bar FROM foo; COMMIT; DELETE FROM foo WHERE rowid > 3000000; UPDATE foo SET bar = 'one' WHERE rowid <= 1000000; UPDATE foo SET bar = 'two' WHERE rowid > 1000000 AND rowid < 2000000; UPDATE foo SET bar = 'three' WHERE rowid > 2000000; CREATE INDEX idx1 ON foo(bar); This creates foo table with 3 mln records: 1 mln with bar = 'one', 1 mln with bar = 'two' and 1 mln with bar = 'three'. Now, the query. EXPLAIN QUERY PLAN SELECT * FROM foo WHERE bar = 'three' and rowid > 2900000 LIMIT 10; SELECT * FROM foo WHERE bar = 'three' and rowid > 2900000 LIMIT 10; While running this query SQLITE reads 18 Mbytes (and it uses idx1 index). 18 MBYTES. Now, run this: ALTER TABLE foo ADD COLUMN id; UPDATE foo SET id = rowid; CREATE INDEX idx2 ON foo(bar, id); And update query: change rowid to id: EXPLAIN QUERY PLAN SELECT * FROM foo WHERE bar = 'three' and id > 2900000 LIMIT 10; SELECT * FROM foo WHERE bar = 'three' and id > 2900000 LIMIT 10; Run query. Now using idx2 index SQLITE reads only about 20 Kbytes! 20 KBYTES. This script proves that using rowid in index is possible and eliminates the need of reading lots of data. But SQLITE is not using it. I think this behaviour is SQLITE defect. _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
