Thanks Igor That makes sense but if I drop MD5 from the query (the vast majority of MD5 values would be null anyway) and use select ID FROM rtable WHERE search > 0 and isf = 0 ORDER BY afo The result from explain query plan is 0|0|0 SCAN TABLE rtable (~33333 rows) >> 0|0|0 USE TEMP B-TREE FOR ORDER BY
Which seems to indicate that the b-tree is still being created (I'll test shortly, but running another long test at the moment) On 21 September 2011 14:33, Igor Tandetnik <itandet...@mvps.org> wrote: > Paul Sanderson <sandersonforens...@gmail.com> wrote: >> select ID FROM rtable WHERE search > 0 and MD5 is NULL and isf = 0 ORDER BY >> afo >> >> explain query plan gives the following for the initial query >> 0|0|0 SEARCH TABLE rtable USING INDEX md5_a (md5=?) (~2 rows) >> 0|0|0 USE TEMP B-TREE FOR ORDER BY >> >> it seems that the extra time is taken creating a b-tree for the order >> by but if correct why is the existing index not used? > > Only one index per table can be used. Imagine you have two lists of (the > same) people, one sorted by first name and one by last name. You need to list > all Peters alphabetically by their last name. You can either use the first > list to find all Peters, then sort them by hand. Or you can use the second > list to enumerate everyone in the order of last name, and select only Peters. > But you can't use both lists. > >> is there anyway of speeding this up? > > A single index on (md5, afo) may help. > -- > Igor Tandetnik > > _______________________________________________ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > -- Paul Sanderson Sanderson Forensics +44 (0)1326 572786 www.sandersonforensics.com http://www.twitter.com/sandersonforens _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
