If I run your sql.txt script with the normalization of kind my first time query
shows 0 seconds.
D:\x>sqlite3 sq1 <sql.txt
0|0|0|SEARCH TABLE log USING INDEX idxlog_kind_computer (kind=? AND computer=?)
(~406234 rows)
Seconds elapsed: 0
Does yours show a longer time than that and/or a different plan?
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 8:16 AM
To: General Discussion of SQLite Database
Subject: EXT :Re: [sqlite]SELECT query first run is VERY slow
>
> select count(*) as cnt,kind,computer
> from log
> group by kind,computer
> order by cnt desc
>
> what happens here?
>
> SELECT *
> FROM log INDEXED BY idxlog_kind_computer
> WHERE kind = 'info' AND computer=1 and id > 7070636
> LIMIT 100;
>
> there are 3_022_148 identical entries 'info,1' in your index
>
> sqlite has to traverse near all of them and so it is not much help with
> binary search.
> Drop this index, run ANALYZE and sqlite will use your primary key quite
> fast.
The problem is: choosing primary key index is not always a good choice.
Sometimes it can lead to scanning half of table records.
Let's assume we have:
CREATE TABLE foo(bar);
CREATE INDEX idx ON foo(bar);
INSERT INTO foo VALUES('one');
...... 1 mln inserts on one......
INSERT INTO foo VALUES('one');
INSERT INTO foo VALUES('two');
...... 1 mln inserts of two......
INSERT INTO foo VALUES('two');
INSERT INTO foo VALUES('three');
...... 1 mln inserts of three......
INSERT INTO foo VALUES('three');
Now, the query:
SELECT * FROM foo WHERE bar='three' and id > 1 LIMIT 10;
If query planner uses primary index it'll have to scan 2 mln records before he
hits first one matching query.
(speaking about "normal" settings, SQLITE scans from lesser rowid to greater)
And if query planner can somehow use index idx with both supported values {
bar:'three', id:1 } there will be no scanning.
It's binary search and it's what I need in my app.
_______________________________________________
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
