join order when using order by

[Christopher Cudennec]

Hi,

we use Hibernate in our current project so I don't always have direct control over the sql that is generated. That is why I try to understand the optimization done by MySQL. When analyzing a (quite complex) SELECT statement, I wondered why the join order stated by "EXPLAIN SELECT ..." was not optimal.

The documentation for "ORDER BY optimization" says that there can be difficulties if "you are joining many tables, and the columns in the ORDER BY are not all from the first non-constant table that is used to retrieve rows. (This is the first table in the EXPLAIN output that does not have a const join type.)".

That is exactly what is the case for me. I can improve the performance by using a SELECT STRAIGHT_JOIN here. But as I said: We use Hibernate and don't have control over the creation of the SQL statement. Is there any possibility to improve the join order that MySQL uses? I have already done "ANALYZE TABLE ..." for all tables that are involved in my query.

Thanks for any suggestions :).

Christopher