Avoid temporary table when using MySQL's ORDER BY

If there is an ORDER BY clause and a different GROUP BY clause, or if the ORDER BY or GROUP BY contains columns from tables other than the first table in the join queue, a temporary table is created.

If all columns in ORDER BY and GROUP BY clauses come from the same table, that table is preferred first when joining.

More tips like this in manual’s WHERE Clause Optimization.

  1. by sobstel • August 2011 • blog archive