I tried taking the tid and verbose tests out of the WHERE clause, and
it made very little difference in the performance. I was thinking
that if I can at least speed it up with just eid and type, I could try
to extend it to the other columns.
Thanks,
Jeff
On Jun 28, 2008, at 6:25 AM, Emilio Platzer wrote:
> (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
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
