Re: [sqlite] Restricting fast no-result query yields slow no-result query
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
[sqlite] Restricting fast no-result query yields slow no-result query
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 p1p2p3p4 p5 comment - - -- - 0 Trace 0 0 000 1 String80 1 0 x 00 2 Goto 0 26000 3 OpenRead 0 2 0 10 00 4 OpenRead 1 8 0 keyinfo(1,BINARY) 00 5 IsNull 1 23000 6 Affinity 1 1 0 ab 00 7 SeekGe 1 231 1 00 8 IdxGE 1 231 1 01 9 IdxRowid 1 2 000 10Seek 0 2 000 11Column 0 0 300 12Column 0 1 400 13Column 0 2 500 14Column 0 3 600 15Column 0 4 700 16Column 1 0 800 17Column 0 6 9 0 00 18Column 0 7 10 00 19Column 0 8 11 00 20Column 0 9 12 00 21ResultRow 3 10000 22Next 1 8 000 23Close 0 0 000 24Close 1 0 000 25Halt 0 0 000 26Transaction0 0 000 27VerifyCookie 0 12000 28TableLock 0 2 0 filebackup 00 29Goto 0 3 000 sqlite> EXPLAIN SELECT * FROM filebackup WHERE sha1='x' AND refid=0; addr opcode p1p2p3p4 p5 comment - - -- - 0 Trace 0 0 000 1 String80 1 0 x 00 2 Integer0 2 000 3 Goto 0 29000 4 OpenRead 0 2 0 10 00 5 OpenRead 1 9 0 keyinfo(1,BINARY) 00 6 IsNull 2 26000 7 Affinity 2 1 0 db 00 8 SeekGe 1 262 1 00 9 IdxGE 1 262 1 01 10IdxRowid 1 3 000 11Seek 0 3 000 12Column 0 5 300 13Ne 1 253 collseq(BINARY) 69 14Column 0 0 500 15Column 0 1 600 16Column 0 2 700 17Column 0 3 800 18Column 0 4 900 19Column 0 5 10 00 20Column 1 0 110 00 21Column 0 7 12 00 22Column 0 8 13 00 23Column 0 9 14 00 24ResultRow 5 10000 25Next 1 9 000 26Close 0 0 000 27Close 1 0 000 28Halt 0 0 000 29Transaction0 0 000 30VerifyCookie 0 12000 31TableLock 0 2 0 filebackup 00 32Goto 0 4 000 -- 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
