Optimize MySQL Query Performance Using Joins Instead of Subqueries

Although MySQL comes with built-in intelligence to automatically optimize joins and subqueries, this optimization is far from perfect. An experienced database architect can often improve query performance by orders of magnitude through simple tweaks to the way queries are written.

For example, MySQL is better at optimizing joins than subqueries, so if you find the load averages on your MySQL server hitting unacceptably high levels, examine your application code and try rewriting your subqueries as joins or sequences of joins. For example, while the following subquery is certainly legal:

SELECT r.RouteID, f.FlightID FROM route AS r, flight AS f
  WHERE r.RouteID = f.RouteID AND r.Status = 1 AND f.AircraftID IN
  (SELECT AircraftID FROM aircraft
   WHERE AircraftTypeID = 616);

the following equivalent join would run faster due to MySQL’s optimization algorithms:

SELECT r.RouteID, f.FlightID FROM route AS r, flight AS f, aircraft AS a
  WHERE r.RouteID = f.RouteID AND f.AircraftID = a.AircraftID
  AND r.Status = 1 AND a.AircraftTypeID = 616;

It’s a good idea to match the fields being joined in terms of both type and length. MySQL tends to be a little inefficient when using indexes on joined fields that are of different lengths and/or types.

You can also turn inefficient queries into more efficient ones through creative use of MySQL’s ORDER BY and LIMIT clauses. Consider the following subquery:

SELECT RouteID, Duration FROM route
  WHERE Duration =
  (SELECT MAX(duration) FROM route);

This works better as the following query, which is simpler to read and also runs much faster:

SELECT RouteID, Duration FROM route
  ORDER BY duration DESC
  LIMIT 0,1;