Super simple ways to optimize MySQL query

Posted 5:34 pm by muni & filed under MYSQL, PHP, SQL.

In this tutorial we are going to discuss super simple ways to optimize MySQL query. Please follow each steps carefully to optimize MySQL query performance that in turns improve the your application performance tremendously.

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.

We have 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.

Enable Slow Queries Log In MySQL via Command Line:

Open your command line window and go to the MySQL bin directory using following command.

cd C:\xampp\mysql\bin

now login into your MySQL server as admin using following query with root admin credentials. where our root user won’t have any password, so just press enter to login.

Now run one by one all the queries like in the below image.

Enable Query log Not Using Indexes (log_queries_not_using_indexes)

This is another simple to find out all the queries, that are not using indexes while fetching records from MySQL server. Now run the blow query to enable query log that are not using indexes.

SET GLOBAL log_queries_not_using_indexes = 'ON'; 

Now run the blow query to check ‘log_queries_not_using_indexes’ variable is enabled in our MySQL server.

SHOW GLOBAL VARIABLES LIKE 'log_queries_not_using_indexes';

This queries will be logged in the MySQL general_log_file.

Add Index and Foreign Key Constraint To Improve Query Speed

If your table columns is not properly indexed then all the queries will run very slowly, that in turn reduces the application performance very badly. For example to fetch single record from the table that contains lakhs of records, then it will check all the lakhs of records to fetch sinlge record. It’s like searching a single page in 1000 pages book, we have to go through each page to get exact page.

If we are indexed our table properly then MySQL fetches that single record very elegant way by going to indexes very easily and quickly. It’s like search a page by going through index of the book and going to exact page very easily.

For example I am having users table with following structure, Only the id column is indexed.

CREATE TABLE IF NOT EXISTS `users` (
  `id` int(11) NOT NULL,
  `email` varchar(75) NOT NULL,
  `password` varchar(75) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

--
-- Indexes for table `users`
--
ALTER TABLE `users`
  ADD PRIMARY KEY (`id`);

While making login request where we will check both email and password of the user against all the user records to authenticate user successfully. In order to optimize our login query we have index both email and password properly like this.

ALTER TABLE `users` ADD INDEX `email_pdx` (`email`, `password`);

To check our index is set properly, then run the following query. It will list out all the indexes from the Users table.

SHOW INDEX FROM users;

While indexing consider following things.
1. Don’t index table columns that have less number records.
2. Index all the table columns that we are used in the WHERE & JOIN conditions.

Use the following queries to add indexes to the table columns.

//Adding Index To the Table;
CREATE INDEX index_name ON table_name(column_name);
//Altering indexes
ALTER TABLE table_name ADD INDEX ( column_name );

Avoid Duplicate Indexes

Over use indexes also causes to slow down Query performance for select, insert and update statements very badly, and also takes up lot of disk space. Use indexes very carefully, so remove the duplicate indexes from the tables. Look at the below table which has duplicate indexes..

CREATE TABLE IF NOT EXISTS `users` (
  `id` int(11) NOT NULL,
  `email` varchar(75) NOT NULL,
  `password` varchar(75) NOT NULL,
  PRIMARY KEY (`id`),
  KEY `email` (`email`),
  KEY `email_1` (`email`),
  KEY `email_password` (`email`,`password`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

So remove the duplicate indexes from the above table.

ALTER TABLE users DROP INDEX email;
ALTER TABLE users DROP INDEX email_1;

Remove Unused Indexes From the MySQL Table

The unused indexes also causes to slow down the select, update and insert statements and takes up lot of disk spaces like duplicate indexes. So manually verify the unused indexes and drop it.

Analyze SQL queries Using MySQL “Explain” Statement

When we do MySQL query performance analyzes, we use “Explain” statement to analyze our queries.

This Explain statement will list out possible indexes used as well number records searched to fetch the record we need. From description we can add index to possible table columns.