On 5 Feb 2010, at 5:05am, Kelly Jones wrote: > I have a query that runs very quickly and returns no results: > > SELECT * FROM filebackup WHERE sha1='x'; > > However, the more restrictive query below runs very slowly, although > it obviously can't have any results either: > > SELECT * FROM filebackup WHERE sha1='x' AND refid=0; > > I have indexes on both sha1 (string) and refid (int). Only issue I > see: this is a large table and refid=0 for 90%+ of the rows. > > The explains are below. Any reason why sqlite3 chooses the 'wrong' > index and/or how to fix this?
It would appear that SQLite3 is doing the wrong thing. There are certain ways of messing about with this but I don't know enough to know why you have to mess with it in the first place. First, try dropping the index on refid, and see if that improves things. Then remake that index, and use the ANALYZE command to see if this make SQLite3 guess right in future: http://www.sqlite.org/lang_analyze.html Simon. _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
