simple ways to optimize MySQL query

Enable Slow Queries Log In MySQL

It’s very difficult to manually to find those queries running very slowly that in turn reduces your application performance. There is simple way to find all the slow queries that is running in our application by enabling slow query log in our MySQL server.

In Windows xampp localhost it is very easy to enable slow query log. All you need to do is just copy the below queries and run in your phpMyAdmin, It will enable slow query in your MySQL server.

SET GLOBAL slow_query_log_file = 'slow_query.log';
SET GLOBAL long_query_time = 10;
SET GLOBAL slow_query_log = 'ON';

After running above SQL query, run the blow SQL query one by one to check MySQL slow query log is enabled or not. 

  
SHOW GLOBAL VARIABLES LIKE 'slow\_%';


It will list out all MySQL variable that starts with “slow” like this.

successfully enabled slow query log in MySQL server. Now run below query to check long_query_time we set.

SHOW GLOBAL VARIABLES LIKE 'long_query_time';

Now access your application via browser, it will log out all slow 
queries that running in particular page of the application. Now go to 
the MySQL server directory ‘data’ folder (C:\xampp\mysql\data), where we
can find file named ‘slow_query.log’. It will contain all slow queries 
that logged.