10

When I do an explain on my query

enter image description here

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?
Community
  • 1
  • 1
Jason Swett
  • 43,526
  • 67
  • 220
  • 351
  • 1
    I think it means "the query optimizer can't use an index on the table to retrieve the data in the final order; the result set is likely to be too big for memory; therefore, the intermediate results will be stored in a file that is then sorted". It is not automatically bad when the DBMS uses temporary files and sorts them; it may be the best way to answer the query (the optimizer thinks it is). – Jonathan Leffler Feb 08 '11 at 16:22
  • 1
    It's using a filesort because it has no index on the sorting fields. Consider adding an index on the required fields. Post your table creation statements and the query if you want us to help/ – Konerak Feb 08 '11 at 17:53

1 Answers1

7

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.

Taras
  • 653
  • 6
  • 15
Galz
  • 6,713
  • 4
  • 33
  • 39