On Jan 29, 2010, at 8:10 PM, Tim Romano wrote: > Dan, > Thanks for that detail about the b-tree for IN-list queries. When I > examine a query plan for a query like the one below: > > explain query plan > select * from title where id IN(10,20,30,40) > > the plan indicates that an index is used (there's a unique index on > title.id) : > > TABLE title WITH INDEX TITLE_ID_UIX
I didn't give you the whole story it seems... In this case, if you have an index on title(id), it will use build the temporary b-tree containing (10,20,30,40), then loop through that table doing lookups on the title(id) index. So, 4 lookups in total. The reason it builds the temporary b-tree at all in this case is in case the user specifies duplicate values (i.e. if title(id) is a unique index, "id IN (10,20,20,30)" should return at most 3 rows, not 4). There is cost based analyzer making the decision. If it determines that a linear scan of table "title" is cheaper than the 4 lookups, it will do that instead. In this case the output of EXPLAIN QUERY PLAN would not mention an index at all, so that isn't happening in your case. Cost based analysis is described in comments in where.c. Function bestBtreeIndex(). Run ANALYZE if SQLite's cost based analyzer is making the wrong decision for your data. Dan. _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
