MySQL explain Query understanding

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?


Thank you for visiting the Q&A section on Magenaut. Please note that all the answers may not help you solve the issue immediately. So please treat them as advisements. If you found the post helpful (or not), leave a comment & I’ll get back to you as soon as possible.

Method 1

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

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.

Method 2


Explain `MySQL Query`

Example: EXPLAIN SELECT * FROM categoriesG

JOIN Country ON (City.CountryCode = Country.Code)
WHERE City.CountryCode = 'IND' AND Country.Continent = 'Asia'G

Explain followed with your mysql query

Better explained in details here

All methods was sourced from or, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0

0 0 votes
Article Rating
Notify of

Inline Feedbacks
View all comments
Would love your thoughts, please comment.x