Very strange. I modified my query to not use verbose or tid, so only
the indexed columns are relevant.
With:
CREATE INDEX ev4_idx ON events(type);
The query runs in about 9 seconds.
With:
CREATE INDEX ev4_idx ON events(type,eid desc)
It runs in 11 seconds.
I'm not using the most accurate timing in the world (not using
database functions for the timing, since I don't know if that would
distort the results) - literally, a wall clock. But it is noticeably
a little slower. Any ideas?
Thanks,
Jeff
On Jun 28, 2008, at 4:29 AM, Alexey Pechnikov wrote:
> В сообщении от 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
