On Wed, 18 Mar 2015 14:43:26 +0100 "Mario M. Westphal" <mw at mwlabs.de> wrote:
> I?m using 3.8.8.1 on Windows via the ?C? interface. > > I work with SQLite for several years with good success. And I know > that no optimizer ever will be perfect. But I?ve just stumbled upon a > case where a very similar query requires between 0.2 seconds and a > whopping 30 seconds. > > I?ve simplified things as much as possible and included the create > instructions and queries below. <...snip...> > The question is: When JOINing large tables with a temporary table, > how to ensure that the optimizer can work optimal? Running ANALYZE > with a temporary table probably does not work, and ANALYZE takes > about 1 minute on this database so this is not feasible for each > query. > > I'm glad to have found an apparently working solution (IN instead of > JOIN) but I wonder if this could be somehow automated by the > optimizer? Or maybe this is a worst-case for the optimizer? > You can run ANALYZE on any table, try: ANALYZE temp_table; SELECT .... You can also test-stress IN and know where is the limit. I think/suppouse/suspect that in this case there is no winning for using a temporal table intstead an IN, it should be faster when the temp table has more columns used in the where clause or additional join restriction. --- --- Eduardo Morras <emorrasg at yahoo.es>
