MySQL explain Query understanding

mysql query-optimization
Ken picture Ken · Nov 3, 2011 · Viewed 51k times · Source

I've read on some blogs and in some articles related to optimization, how to optimize queries. I read I need to use indexes and make sure all my primary key and foreign keys are set correctly using a good relational database schema.

Now I have a query I need to optimize and I get this on the EXPLAIN:

Using where; Using temporary; Using filesort

I am using MySQL 5.5

I know I am using WHERE but not with my temporary table nor filesort? What does this mean?

Answer

Abhay picture Abhay · Nov 3, 2011

Using temporary means that MySQL need to use some temporary tables for storing intermediate data calculated when executing your query.

Using filesort is a sorting algorithm where MySQL isn't able to use an index for sorting and therefore can't do the complete sort in memory. Instead it breaks the sort into smaller chunks and then merge the results to get the final sorted data.

Please refer to http://dev.mysql.com/doc/refman/5.0/en/explain-output.html.

I think you might be using an ORDER BY plus some derived table or sub-query. It would be great if you could paste your query and relevant tables/indexes information and the EXPLAIN output.

Related questions

mysql select from n last rows

I have a table with index (autoincrement) and integer value. The table is millions of rows long. How can I search if a certain number appear in the last n rows of the table most efficiently?

Read More
JOIN queries vs multiple queries

Are JOIN queries faster than several queries? (You run your main query, and then you run many other SELECTs based on the results from your main query) I'm asking because JOINing them would complicate A LOT the design of my …

Read More
How to do the Recursive SELECT query in MySQL?

I got a following table: col1 | col2 | col3 -----+------+------- 1 | a | 5 5 | d | 3 3 | k | 7 6 | o | 2 2 | 0 | 8 If a user searches for "1", the program will look at the col1 that has "1" then it will get a value in col3 "5", then the program …

Read More