MySQL Optimization With Slow Query Logs & EXPLAIN Statement

MySQL optimization is key to have a successful project deployment. If you are developing an application which is going to be used by a huge audience on a regular basis then you need to make sure that your database and queries are optimized in order to provide stress-free user experience.

Logging is a very important factor as this gives you an insight of how the application is being used and helps find if you have made some honest mistakes which maybe degrading your app performance. Once of the most important logs that you can configure in MySQL is the Slow Query Logs. As the name suggests this basically logs those queries which may be expensive to execute. You can then analyze these queries and try to refactor them so that the query can run faster.

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.

Level: Advanced

Technologies: MySQL

post via Codincafe

MySQL Optimization With Slow Query Logs & EXPLAIN Statement