Getting rid of “Using temporary; Using filesort”

Question

When I do an explain on my query

I see that it has "Using temporary; Using filesort" under "Extra" for the first row. I understand this is bad but I don't know what exactly it means or how to fix it.

If you want to see my query, here's a more general question I asked about the same query: MySQL query optimization and EXPLAIN for a noob. For reference, the query involves 24 tables and 23 joins.

My questions now are:

• What do "Using temporary" and "Using filesort" mean?
• Assuming they're bad, how do I get rid of them?

Answer 1

As said already, "using filesort" and "using temporary" do not always imply bad performance.

Here are some basic guidelines for improving performance of ORDER BY statements. The highlights:

If you want to increase ORDER BY speed, check whether you can get MySQL to use indexes rather than an extra sorting phase. If this is not possible, you can try the following strategies:

Increase the size of the sort_buffer_size variable.

Increase the size of the read_rnd_buffer_size variable.

Use less RAM per row by declaring columns only as large as they need to be to hold the values stored in them. For example, CHAR(16) is better than CHAR(200) if values never exceed 16 characters.

First try to use indices (make sure the fields you are sorting by have indices). Note that increasing the system variables sort_buffer_size and read_rnd_buffer_size can also have a negative effect on other queries - consider setting them specifically for the session you need them for and leave them at default for all other sessions.