tl;dr: I'm looking for an elegant way to eager load related rows using separate IN() queries, because MySQL is "Using temporary; Using filesort" when I try subquery eager loading.
My first attempt used joined eager loading, but that was causing performance issues on my queries with nested relationships. This is because joined eager loading uses a LEFT OUTER JOIN which loads the full set of columns represented by the parents on each row of the results. The "What Kind of Loading to Use?" <http://docs.sqlalchemy.org/en/latest/orm/loading_relationships.html#what-kind-of-loading-to-use> part of the docs explains the issue pretty well. Next, I tried switching to subquery eager loading to fix the problem. But, MySQL 5.6 says "Using temporary; Using filesort" when I run an explain on the query. It did seem to make the query run ~20% faster, but I'm worried about the load that would put on my database. Example: https://gist.github.com/pawl/bde2737c4d217b468eba1107a03fbcb5 Next, I was hoping I could get SQLAlchemy to use its own internal storage in the session to find related rows if I already queried for them. However, this only seems to work one way by default. It will still run queries (if you’re not doing eager loading) if you access relations from the “one” side of a one to many relationship. Example: https://gist.github.com/pawl/abc0e536219144e569c728c8590b0d39 At this point, I'm really wishing there was an eager loading strategy that queries for the related rows using IN(). So, I read the "Creating Custom Load Rules" <http://docs.sqlalchemy.org/en/latest/orm/loading_relationships.html#creating-custom-load-rules> section of the docs and came up with an example that added the related rows with "set_committed_value" after I queried for them separately: https://gist.github.com/pawl/df5ba8923d9929dd1f4fc4e683eced40 Is there a more elegant way to do this? And, has adding an IN() eager loading strategy to SQLAlchemy been considered? -- SQLAlchemy - The Python SQL Toolkit and Object Relational Mapper http://www.sqlalchemy.org/ To post example code, please provide an MCVE: Minimal, Complete, and Verifiable Example. See http://stackoverflow.com/help/mcve for a full description. --- You received this message because you are subscribed to the Google Groups "sqlalchemy" group. To unsubscribe from this group and stop receiving emails from it, send an email to sqlalchemy+unsubscr...@googlegroups.com. To post to this group, send email to sqlalchemy@googlegroups.com. Visit this group at https://groups.google.com/group/sqlalchemy. For more options, visit https://groups.google.com/d/optout.
