[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
