SQLite doesn't have this type of optimization. Internally IN-list will be converted by SQLite parser to sequence of equality checks that will be performed on each row. So generally the more the IN-list the worse the performance of the query. So with IN-list growing to 100 or more elements I'd say the performance would be much worse than with temporary table with index on the value. Without index it will be the same unless query is such that temporary table can become driving one and another table has an index on the field in front of IN-list.
Pavel On Thu, Jan 28, 2010 at 10:26 AM, Tim Romano <tim.rom...@yahoo.com> 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. > > Thanks > Tim Romano > > > On 1/27/2010 12:28 AM, Jay A. Kreibich wrote: >> [....] temp database are always cleaned up when the database >> connection is closed. And since temp tables and indexes go into the >> temp database, and not your main active database, there is no >> long-term maintenance. >> > > _______________________________________________ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
