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.
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';
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
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.
post via Codincafe