It's all about caching.
If OS has already cached index data query executes in less than a second time.
To understand what is actually happening you should monitor reading count of
SQLITE console process (or your app that is executing).
There shouldn't be reading of more than 1 Mb if SQLITE is using index properly.
26 июля 2011, 20:27 от "Black, Michael (IS)" <michael.bla...@ngc.com>:
> 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
