Azure Database for MySQL is a PaaS (Platform as a Service) solution that Microsoft offers on Azure. Using Azure managed services for MySQL enables one to easily build an intelligent and secure application.

It often happens that a simple query can become a bottleneck impacting the entire database performance. MySQL integrates a very useful tool – the EXPLAIN statement which is capable of profiling client queries and helps in identifying the root causes of slow queries. One can use it to get information about how SQL statements are carried out. With the help of this information, one can easily profile which queries are running slow and what are the reasons behind it.

The output below shows an example of the execution of an EXPLAIN statement.

 

Explain statement example

 

In the above example, the value of the key is NULL. This means that MySQL is unable to find any indexes optimized for the query and it performs a full table scan. Let’s get this query optimized by adding an index on the ID column.

 

Explain statement indexed by ID

 

The new EXPLAIN statement shows that MySQL will now use an index to limit the number of rows to one, which in turn notably shortens the search time.

 

Pro Tip: Using MySQL Sys Schema to optimize a database.

Covering index

A covering index has all columns of the query in the index to reduce value retrieval from data tables. To explain this, see the GROUP BY statement below.

 

Group By statement

 

As seen in the output, MySQL does not use indexes because of the lack of availability of any proper one. The output also shows “Using temporary; Using filesort”, which means MySQL will have a temporary table created in order to satisfy the “GROUP BY” clause.


No difference will be made by creating an index on ‘c2’ alone, and MySQL still needs to create a temporary table:

 

Temporary table

 

In this case, one can create a covered index on both “c1” and “c2” by adding the value of “c2” in the index directly so to eliminate further data lookup. 

 

Covered index

 

MySQL will now use the covered index and avoid creating a temporary table.

 

Bonus Tip:  How to auto-scale Azure SQL.

Combined index

A combined index comprises values from multiple columns and can be taken as an array of rows that are sorted by concatenating values of the indexed columns. This can be useful in a GROUP BY statement.

 

Combined index

 

MySQL performs a somewhat slow “filesort” operation, especially when it has a lot of rows to sort. If you wish to optimize this query, you can have a combined index created on both columns that are to be sorted. The EXPLAIN plan now shows that MySQL can use the combined index in order to avoid additional sorting as the index is already sorted.

 

Combined index on sorted columns

Conclusion

The use of EXPLAIN and different types of indexes can increase performance in a very significant manner. Using an index doesn’t mean that MySQL can use it for your queries. Make sure to validate your assumptions using EXPLAIN and get your queries optimized using indexes.