On 26-07-2011 10:30, Григорий Григоренко wrote:
>>
>> I thought that, if you have an index on a,b,c,d
>> than you should not have an index on a,b,c too
>> because if you use those 3 field in the where-clause, use can be made of
>> the 4-field index
>>
>
> I'm not sure. Let me explain.
>
> I need query to be ORDER BY id DESC. I've dropped this ORDER BY to simplify
> my case. But in real-life app I need it.
>
> So, index on (kind, computer) has these index records:
>
> [ KIND ] [ COMPUTER ] [ ID ]
> ...
>
> They are ordered by kind, computer and then by id. So ORDER BY id DESC comes
> free.
> Query planner just need to retrieve records from subset of index records
> starting at last one backwards.
>
> Index on (kind,computer, process,who) has these index records:
>
> [ KIND ] [ COMPUTER ] [ PROCESS ] [ WHO ] [ ID ]
>
> This time having found subset of index records query planner cannot start
> retrieving them from the last to first.
> It has to sort them by ID at first. Isn't it?
>
>
> Maybe I'm not getting it right?
I'm not sure, but you might have a good point....
I was looking at your db, and did this, which did lead me to the
suggestion i made earlier:
C:\TEMP>sqlite3.exe 2011-07-24.dblite
SQLite version 3.7.2
Enter ".help" for instructions
Enter SQL statements terminated with a ";"
sqlite> .schema
CREATE TABLE T(t);
CREATE TABLE item(name text,value text);
CREATE TABLE kind(id integer,kind text);
CREATE TABLE log(id integer primary key autoincrement,msg
text,created_at int,kind,computer,process,who);
CREATE UNIQUE INDEX idxitem_name_value ON item(name,value);
CREATE INDEX idxlog_created_at ON log(created_at);
CREATE INDEX idxlog_kind_computer_process_who ON
log(kind,computer,process,who);
sqlite>
sqlite> .read sql.txt
0|0|TABLE log WITH INDEX idxlog_kind_computer_process_who
2011-07-26 20:13:43.423
2011-07-26 20:13:43.438
sqlite>
sql.txt:
CREATE TABLE IF NOT EXISTS T(t); DELETE FROM T; INSERT INTO T VALUES(
strftime("%Y-%m-%d %H:%M:%f", "now") );
EXPLAIN QUERY PLAN SELECT * FROM log WHERE kind = 'info' AND computer=1
and id > 7070636 LIMIT 100;
SELECT * FROM log WHERE kind = 'info' AND computer=1 and id > 7070636
LIMIT 100;
INSERT INTO T VALUES( strftime("%Y-%m-%d %H:%M:%f", "now") );
SELECT * FROM T ;
And, YES, i did do a VACUUM and an ANALYZE before doing the query.
--
Luuk
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
