MySQL Optimization With Slow Query Logs & EXPLAIN Statement
Slow Query Log Configuration
By default Slow Query Logs are not enabled in a vanilla MySQL installation. You can cross check the status of the configuration by using
SHOW VARIABLES LIKE 'slow_query_log';
If the value of the above statement is OFF then use the below to enable Slow Query Logging
SET GLOBAL slow_query_log = 'ON';
The below query will give you the path where the log file will be stored
SHOW VARIABLES LIKE 'slow_query_log_file';
To change the default path of the log file you can use
SET GLOBAL slow_query_log_file = '/path/filename';
By default MySQL will log only those queries which take more than 10 seconds to execute. You can change this by using the below query by replacing ‘X’ to desired number of seconds
SET GLOBAL long_query_time = X;
To turn off the Slow Query Logging simply use
SET GLOBAL slow_query_log = 'OFF';
Using EXPLAIN Statement
Once you have the Slow Query Log enabled you can watch the file to see the queries being logged. You can take those queries and use the EXPLAIN statement to get an insight on what’s happening with the query.
Just add EXPLAIN before your SELECT statement and the result will give you the following bits of information
EXPLAIN SELECT * FROM sales
- id – A sequential identifier for each SELECT within the query (if you have nested subqueries)
- select_type – The type of SELECT query. Possible values are:
SIMPLE, PRIMARY, DERIVED, SUBQUERY, DEPENDENT SUBQUERY, UNCACHEABLE SUBQUERY, UNION, DEPENDENT UNION, UNION RESULT etc. - table – The table referred to by the row
- type – Indicates missing indexes or how the query that is written should be reconsidered. Possible values are:
system, const, eq_ref, ref, fulltext, ref_or_null, index_merge, unique_subquery, index_subquery,
range, index or all - possible_keys – Shows the keys that may or may not be used in practice to find rows from the table. If the column is NULL, it indicates no relevant indexes could be found.
- key – Indicates the actual index used by MySQL, this is often the optimal choice.
- key_len – Indicates the length of the index the Query Optimizer chose to use.
- ref – Shows the columns or constants that are compared to the index named in the key column. MySQL will either pick a constant value to be compared or a column itself based on the query execution plan.
- rows – Lists the number of records that were traversed to produce the output.
- Extra – Contains additional execution plan information for the query. Values such as “Using temporary”, “Using filesort”, etc. in this column may indicate a troublesome query.
SQL Query Execution Order
Here is a table which helps to understand the execution order of an SQL query. Understanding query order can help you diagnose why a query won’t run, and even more frequently will help you optimize your queries to run faster.
