Part of the problem is it seems you can't create an index with rowid:
3.7.5 sqlite> create table t(i int); sqlite> create index idx1 on t(i); sqlite> create index idx2 on t(i,rowid); Error: table t has no column named rowid Any particular reason it can't be included in an index? Michael D. Black Senior Scientist NG Information Systems Advanced Analytics Directorate ________________________________ From: sqlite-users-boun...@sqlite.org [sqlite-users-boun...@sqlite.org] on behalf of Григорий Григоренко [grigore...@mail.ru] Sent: Tuesday, July 26, 2011 11:50 AM To: General Discussion of SQLite Database Subject: EXT :Re: [sqlite]SELECT query first run is VERY slow 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 _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
