MySQL Performance Boosting with Indexes and Explain

Techniques to improve application performance can come from a lot of different places, but normally the first thing we look at --- the most common bottleneck --- is the database. Can it be improved? How can we measure and understand what needs and can be improved?

One very simple yet very useful tool is query profiling. Enabling profiling is a simple way to get a more accurate time estimate of running a query. This is a two-step process. First, we have to enable profiling. Then, we call show profiles to actually get the query running time.

Let's imagine we have the following insert in our database (and let's assume User 1 and Gallery 1 are already created):

INSERT INTO `homestead`.`images` (`id`, `gallery_id`, `original_filename`, `filename`, `description`) VALUES
(1, 1, 'me.jpg', 'me.jpg', 'A photo of me walking down the street'),
(2, 1, 'dog.jpg', 'dog.jpg', 'A photo of my dog on the street'),
(3, 1, 'cat.jpg', 'cat.jpg', 'A photo of my cat walking down the street'),
(4, 1, 'purr.jpg', 'purr.jpg', 'A photo of my cat purring');    

Obviously, this amount of data will not cause any trouble, but let's use it to do a simple profile. Let's consider the following query:

SELECT * FROM `homestead`.`images` AS i
WHERE i.description LIKE '%street%';

This query is a good example of one that can become problematic in the future if we get a lot of photo entries.

To get an accurate running time on this query, we would use the following SQL:

set profiling = 1;
SELECT * FROM `homestead`.`images` AS i
WHERE i.description LIKE '%street%';
show profiles;

The result would look like the following:

Query_Id	Duration	Query
1	0.00016950	SHOW WARNINGS
2	0.00039200	SELECT * FROM homestead.images AS i \nWHERE i.description LIKE \'%street%\'\nLIMIT 0, 1000
3	0.00037600	SHOW KEYS FROM homestead.images
4	0.00034625	SHOW DATABASES LIKE \'homestead\
5	0.00027600	SHOW TABLES FROM homestead LIKE \'images\'
6	0.00024950	SELECT * FROM homestead.images WHERE 0=1
7	0.00104300	SHOW FULL COLUMNS FROM homestead.images LIKE \'id\'

As we can see, the show profiles; command gives us times not only for the original query but also for all the other queries that are made. This way we can accurately profile our queries.

But how can we actually improve them?

We can either rely on our knowledge of SQL and improvise, or we can rely on the MySQL explain command and improve our query performance based on actual information.

Explain is used to obtain a query execution plan, or how MySQL will execute our query. It works with SELECT, DELETE, INSERT, REPLACE, and UPDATE statements, and it displays information from the optimizer about the statement execution plan. The official documentation does a pretty good job of describing how explain can help us:

With the help of EXPLAIN, you can see where you should add indexes to tables so that the statement executes faster by using indexes to find rows. You can also use EXPLAIN to check whether the optimizer joins the tables in an optimal order.

The post MySQL Performance Boosting with Indexes and Explain appeared first on SitePoint.

by Claudio Ribeiro via SitePoint