When to use STRAIGHT_JOIN with MySQL

by

I wouldn’t recommend using STRAIGHT_JOIN without a good reason. My own experience is that the MySQL query optimizer chooses a poor query plan more often than I’d like, but not often enough that you should just bypass it in general, which is what you would be doing if you always used STRAIGHT_JOIN.

My recommendation is to leave all queries as regular JOINs. If you discover that one query is using a sub-optimal query plan, I would suggest first trying to rewrite or re-structure the query a bit to see if the optimizer will then pick a better query plan. Also, for innodb at least, make sure it’s not just that your index statistics are out-of-date (ANALYZE TABLE). That can cause the optimizer to choose a poor query plan. Optimizer hints should generally be your last resort.

Another reason not to use query hints is that your data distribution may change over time, or your index selectivity may change, etc. as your table grows. Your query hints that are optimal now, may become sub-optimal over time. But the optimizer will be unable to adapt the query plan because of your now outdated hints. You stay more flexible if you allow the optimizer to make the decisions.

More Related Contents:

  1. What’s the difference between INNER JOIN, LEFT JOIN, RIGHT JOIN and FULL JOIN? [duplicate]
  2. How can I do three table JOINs in an UPDATE query?
  3. 1052: Column ‘id’ in field list is ambiguous
  4. MySQL: Quick breakdown of the types of joins [duplicate]
  5. MySQL update table based on another tables value
  6. Required to join 2 tables with their FKs in a 3rd table
  7. MySQL join with where clause
  8. JOIN queries vs multiple queries
  9. Why does MySQL report a syntax error on FULL OUTER JOIN?
  10. conditional join in mysql
  11. MYSQL Select MAX Date inside a join statement
  12. CROSS/OUTER APPLY in MySQL
  13. Mixing ANSI 1992 JOINs and COMMAs in a query
  14. a count for each join – optimisation
  15. Cumulative sum over a set of rows in mysql
  16. MySql Count cannot show 0 values
  17. What’s the difference between comma separated joins and join on syntax in MySQL? [duplicate]
  18. MySQL update a joined table
  19. MySQL join query using like?
  20. MySQL JOIN with LIMIT 1 on joined table
  21. Laravel join with 3 Tables
  22. MySQL Insert & Joins
  23. mySQL – Create a New Table Using Data and Columns from Three Tables
  24. Count with IF condition in MySQL query
  25. How to join two tables using a comma-separated-list in the join field
  26. Mysql select where not in table
  27. When to use a left outer join?
  28. In what order are MySQL JOINs evaluated?
  29. MySQL cartesian product between two SELECT statements
  30. MySQL Multiple Joins in one query?

Leave a Comment