coleifer/peewee — pull request #381 by jpttrssn (2014-06-30 16:11:52+00:00)
Title: MySQL Prefetch with Limit Fix

MySQL does not support using LIMIT and IN subquery. To fix you have to run the subquery first and then pass in the result. This will add 1 additional query for every subquery passed to prefetch. This approach causes errors in test_prefetch_non_pk_fk, test_prefetch_subquery and test_prefetch_subquery_same_depth due to there being no database to run the subquery on and fails on test_prefetch_multi_depth and test_prefetch_simple because of the additional queries.

There is a potential work around that would reduce this back to the optimal number of queries by running the outer queries when needed without changing the select, storing the result, and getting the necessary list of foreign keys to use for the inner query result. This would be a decent amount of changes and I defer to you to decide if this is worth it for this specific case and whether this solution is MySQL only. There may be a performance benefit here when you no longer need to run the subquery inside the IN clause, but it could also be worse for larger datasets? Is passing in a list of 1,000 or 10,000 foreign keys to an IN clause problematic? Perhaps there could be a flag that you set on whether you want to use a subquery in the IN clause or a list of results? You would probably know better.