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 Does SQLite iterate every item in the unique index and look for it in the transient b-tree structure? And if so, does SQLite do this regardless of the relative number of items in each structure, index versus b-tree? We could have 1,000,000 titles and 200 items in the IN-list, but each of the million items would be looked for in the b-tree? Regards Tim Romano On 1/28/2010 12:26 PM, Dan Kennedy wrote: > On Jan 28, 2010, at 10:26 PM, Tim Romano wrote: > > >> Thanks for this clarification. >> >> Wouldn't SQLite simply rewrite my IN-list query, transparently, as an >> equijoin against a transient table that has been populated with the >> values in the IN-list? I don't understand why the IN-list should have >> to be avoided. >> > It creates a temporary b-tree structure and inserts all the values in > the IN(...) clause into it. Then for each row evaluating "? IN (...)" > can be done with a single lookup in the b-tree. > > Dan. > _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
