SQL EXPLAIN: The “Extra” column

This column contains additional information about how MySql resolved the query. There is a line in the manual which states:

If you want to make your queries as fast as possible, look out for Extra values of Using filesort and Using temporary.

Let’s examine why those two are bad.

  • Using filesort

If you see this, it means that an extra pass has been made through the data, in order to sort it. This is not as efficient, and indicates that you may be able to improve query performance.

  • Using temporary

This means that a temporary table will have to be created to hold the result. The manual states that this typically happens if the query contains GROUP BY and ORDER BY clauses that list columns differently.

The MySQL Manual has a fascinating page about ORDER BY Optimization.