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? sqlite> EXPLAIN SELECT * FROM filebackup WHERE sha1='x'; addr opcode p1 p2 p3 p4 p5 comment ---- ------------- ---- ---- ---- ------------- -- ------------- 0 Trace 0 0 0 00 1 String8 0 1 0 x 00 2 Goto 0 26 0 00 3 OpenRead 0 2 0 10 00 4 OpenRead 1 8 0 keyinfo(1,BINARY) 00 5 IsNull 1 23 0 00 6 Affinity 1 1 0 ab 00 7 SeekGe 1 23 1 1 00 8 IdxGE 1 23 1 1 01 9 IdxRowid 1 2 0 00 10 Seek 0 2 0 00 11 Column 0 0 3 00 12 Column 0 1 4 00 13 Column 0 2 5 00 14 Column 0 3 6 00 15 Column 0 4 7 00 16 Column 1 0 8 00 17 Column 0 6 9 0 00 18 Column 0 7 10 00 19 Column 0 8 11 00 20 Column 0 9 12 00 21 ResultRow 3 10 0 00 22 Next 1 8 0 00 23 Close 0 0 0 00 24 Close 1 0 0 00 25 Halt 0 0 0 00 26 Transaction 0 0 0 00 27 VerifyCookie 0 12 0 00 28 TableLock 0 2 0 filebackup 00 29 Goto 0 3 0 00 sqlite> EXPLAIN SELECT * FROM filebackup WHERE sha1='x' AND refid=0; addr opcode p1 p2 p3 p4 p5 comment ---- ------------- ---- ---- ---- ------------- -- ------------- 0 Trace 0 0 0 00 1 String8 0 1 0 x 00 2 Integer 0 2 0 00 3 Goto 0 29 0 00 4 OpenRead 0 2 0 10 00 5 OpenRead 1 9 0 keyinfo(1,BINARY) 00 6 IsNull 2 26 0 00 7 Affinity 2 1 0 db 00 8 SeekGe 1 26 2 1 00 9 IdxGE 1 26 2 1 01 10 IdxRowid 1 3 0 00 11 Seek 0 3 0 00 12 Column 0 5 3 00 13 Ne 1 25 3 collseq(BINARY) 69 14 Column 0 0 5 00 15 Column 0 1 6 00 16 Column 0 2 7 00 17 Column 0 3 8 00 18 Column 0 4 9 00 19 Column 0 5 10 00 20 Column 1 0 11 0 00 21 Column 0 7 12 00 22 Column 0 8 13 00 23 Column 0 9 14 00 24 ResultRow 5 10 0 00 25 Next 1 9 0 00 26 Close 0 0 0 00 27 Close 1 0 0 00 28 Halt 0 0 0 00 29 Transaction 0 0 0 00 30 VerifyCookie 0 12 0 00 31 TableLock 0 2 0 filebackup 00 32 Goto 0 4 0 00 -- We're just a Bunch Of Regular Guys, a collective group that's trying to understand and assimilate technology. We feel that resistance to new ideas and technology is unwise and ultimately futile. _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
