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.
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.
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.
[su_note note_color=”#eeeeee” text_color=”#151212″]Pro Tip: Using MySQL Sys Schema to optimize a database. [/su_note]
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.
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:
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.
MySQL will now use the covered index and avoid creating a temporary table.
[su_note note_color=”#eeeeee” text_color=”#151212″]Bonus Tip: How to auto-scale Azure SQL. [/su_note]
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.
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.
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.
See how Netreo delivers value and automates on MySQL Azure Monitoring tasks.
