(sorry about my poor english) I think that the problem doesn't correct by creating a DESC index. The problema was at de 'where clausula':
(events.eid<=3261976) For some reason SQLITE doesn't optimize the query to use the index to locate the last index item that have type=22 and eid<=3261976. Of course if you have only a few items that have tid=9, SQL must have to read every item starting with de last until he find the item that haves tid=9. Do you try to the prevoius sugest: add a index that have type and tid? You must know that, SQLite must read the items to find verbose<=1 good luck! Emilio Alexey Pechnikov escribio': > В сообщении от Saturday 28 June 2008 02:28:05 Jeff Gibson написал(а): >> When I do the following query: >> >> SELECT events.* FROM events WHERE ( events.type=22) AND ( events.tid=9) >> AND (events.eid<=3261976) AND (events.verbose<=1) ORDER BY events.eid >> DESC LIMIT 1; >> >> it's very slow. If I switch the ORDER BY to "ASC" instead of "DESC", >> it's very fast. > > As described in http://www.sqlite.org/lang_createindex.html > "sql-statement ::= CREATE [UNIQUE] INDEX [IF NOT EXISTS] [database-name .] > index-name > ON table-name ( column-name [, column-name]* ) > column-name ::= name [ COLLATE collation-name] [ ASC | DESC ]" > > You can try create additional index as > CREATE INDEX ev4_idx ON events (type,eid desc); > > _______________________________________________ > 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
