> > Sorry, but SQLite does not understand how the subquery (inside the > brackets) is going to be used by the main query. It hqs to complete the > subquery first and only then can it inspect the main query to find out how > to optimize it. This is not a bug, there just isn't enough flexibility to > do this the way you want.
This is unfortunate, especially for an RDBMS aiming to minimize memory consumption for embedded systems. I believe there are many other queries where optimizing the subqueries with regard to the external query will result in optimal memory usage. > One of the following may or may not be useful: > > You may be able to use > > select c1, c2, myagg(*) from ( > select c1, c2 from tableA group by c1,c2 > union all > select c1, c2 from tableB group by c1,c2 > ) group by c1,c2 > > Thank you for your suggestion. But the query plan is the same, how would that be any faster? We actually run an experiment and it is a bit slower, I suppose because of the additional overhead of creating each group in the subquery, which doesn't contribute anything given the way the external query is handled. Alternatively, is there a good reason for tableA and tableB not to be > merged with, perhaps, an extra column indicating 'A' or 'B' ? This would > allow you to create an index and get your answer almost instantly. When > you see two tables with the same columns it's often an indication that > there should really be one table. > This is an alternative we wish to explore, but in our usecase it would be considered a hack, and might cause side-effects in other parts of the code. If we are forced to do it, we might be able to pull it off though. Thank you for your suggestion. Best, Manos > > Simon. > _______________________________________________ > sqlite-users mailing list > [email protected] > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > _______________________________________________ sqlite-users mailing list [email protected] http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
