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
