I see. Do you have any suggestions on where I can find out about how
to get more specific timing information? I'm pretty much an sqlite
novice.
Thanks,
Jeff
On Jun 28, 2008, at 12:02 PM, Stephen Woodbridge wrote:
> [EMAIL PROTECTED] wrote:
>> 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?
>
> Well a few thoughts:
>
> 1) and index on (type) vs (type,eid,desc) will be smaller and get more
> entries per page so you wil like have to bring less pages into memory.
>
> 2) I'm not sure of the statistics in your various indexes, but you
> would
> have to trade off whether or not the added resolution of
> (type,eid,desc)
> adds more value, then finding N records at (type) and doing a
> seqential
> scan of those vs find less and N records at (type,eid,desc) and
> returning them.
>
> I think you need to look at each with more specific timing stats and
> be
> aware of what the cache status of pages are when you run the tests
> because this might affect the timing results.
>
> -Steve
>
>> 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
>
> _______________________________________________
> 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
