What is MySQL STRAIGHT_JOIN and when to use it?

I carefully hand-craft my SQL queries and check them with an EXPLAIN statement. So it came as a surprise that my highly optimized SQL query became so sloooow. After short investigation I discovered that MySQL optimizer changes the order in which tables are joined. In most cases the optimizer is right. In my case it was also right at the beginning of the project lifetime – but later it reordered them in suboptimal order.

By using STRAIGHT_JOIN instead of “regular” inner join I made my sql query optimized again:

STRAIGHT_JOIN is an inner join where MySQL optimizer will not change the order of tables when joining them. It will always read the left table first and then the right table.

In general, you should  put to the left  the table which would give smaller result set in the final result.

Since premature optimization is the root of all evil, you should use “regular” inner join, monitor you application performance and check MySQL slow log. And if the sql query “suddenly” become slow – now you know how to fix it.