2
votes

My code currently executes queries just fine in the SQLAlchemy ORM layer like so:

session().query(model_a).join(
    (model_b, something == somethingelse)
).join(
    (model_c, something == somethingelse)
) # etc ...

But I've come across a query that was taking minutes to finish. Turns out MySQL is to blame because it's not joining in the order we ask it to. Adding STRAIGHT_JOIN after SELECT like:

SELECT STRAIGHT_JOIN table_a.id FROM table_a INNER JOIN table_b ...

Fixes the problem but I can't figure out if there's a way to make SQLAlchemy add STRAIGHT_JOIN after SELECT. The docs suggest that hints can be added to select() via with_hint(table_a, "STRAIGHT_JOIN") but this adds the text in the wrong place which causes a MySQL syntax error. This would also involve stepping outside of the ORM which is not ideal.

I've tried using the various MySQL debugging techniques like EXPLAIN, ANALYZE TABLE etc... but can't seem to find out why MySQL is choosing the wrong path. Manually switching around the order of the joins doesn't seem to help either so I'm now in a position where I need to get STRAIGHT_JOIN to work.

Any ideas?

1

1 Answers

5
votes

we support this directly just for the case of MySQL and their strange keywords:

session().query(model_a).prefix_with("STRAIGHT_JOIN").join(
    (model_b, something == somethingelse)
).join(
    (model_c, something == somethingelse)
) # etc ...

http://docs.sqlalchemy.org/en/rel_0_8/orm/query.html?highlight=query.prefix_with#sqlalchemy.orm.query.Query.prefix_with