Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

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?

like image 316
Ken Avatar asked Nov 03 '11 18:11

Ken


People also ask

How read explain in MySQL?

In MySQL, EXPLAIN can be used in front of a query beginning with SELECT , INSERT , DELETE , REPLACE , and UPDATE . For a simple query, it would look like the following: EXPLAIN SELECT * FROM foo WHERE foo. bar = 'infrastructure as a service' OR foo.

Does explain actually run the query?

It tells you the execution plan. It doesn't execute the query (although it might execute subqueries).

How do you optimize MySQL query using explain?

Optimizing Queries with EXPLAINIt displays information from a built-in MySQL optimizer regarding the statement execution plan and the number of rows scanned in each table. Thus we can determine the cost of the query. The query below shows how EXPLAIN works with the SELECT statement.

How does explain work in SQL?

In SQL, EXPLAIN keyword provides a description of how the SQL queries are executed by the databases. These descriptions include the optimizer logs, how tables are joined and in which order etc. Hence, it would be a beneficial tool in query optimization and knowing the details of its execution step by step.


2 Answers

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.

like image 65
Abhay Avatar answered Oct 01 '22 09:10

Abhay


Syntax:

Explain `MySQL Query` 

Example: EXPLAIN SELECT * FROM categoriesG

Example: EXPLAIN EXTENDED SELECT City.Name FROM City 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

like image 33
Aditya P Bhatt Avatar answered Oct 01 '22 10:10

Aditya P Bhatt