Using EXPLAIN to profile slow queries in Azure Database for MySQL
Azure Database for MySQL is a PaaS (Platform as a Service) solution that Microsoft offers on Azure. Using Azure managed services for MySQL (and PostgreSQL), enables one to easily build an intelligent and secure application.
Though Microsoft has done a lot of work to optimize database performance, sometimes a simple query can easily become a bottle neck impacting overall database performance. Luckily, MySQL integrates a handy tool – the EXPLAIN statement – that can profile client queries and thus help you identify the root cause of a slow query. You can use an EXPLAIN statement to get information about how SQL statements are executed. With this information, you can profile which queries are running slow and why.
The output below shows an example of the execution of an EXPLAIN statement.
mysql> EXPLAIN SELECT * FROM tb1 WHERE id=100G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: tb1
partitions: NULL
type: ALL
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: 995789
filtered: 10.00
Extra: Using where
As you can see from this example, the value of key is NULL. This means that MySQL cannot find any indexes optimized for the query and it performs a full table scan. Let's optimize this query by adding an index on the ID column.
mysql> ALTER TABLE tb1 ADD KEY (id);
mysql> EXPLAIN SELECT * FROM tb1 WHERE id=100G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: tb1
partitions: NULL
type: ref
possible_keys: id
key: id
key_len: 4
ref: const
rows: 1
filtered: 100.00
Extra: NULL
The new EXPLAIN statement shows that MySQL will use now an index to limit the number of rows to one, which in turn dramatically shortens the search time.
Covering index
A covering index consists of all columns of your query in the index to reduce value retrieval from data tables. To illustrate this, look at the GROUP BY statement below.
mysql> EXPLAIN SELECT MAX(c1), c2 FROM tb1 WHERE c2 LIKE '%100' GROUP BY c1G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: tb1
partitions: NULL
type: ALL
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: 995789
filtered: 11.11
Extra: Using where; Using temporary; Using filesort
As you can see in the output, MySQL does not use any indexes because no proper indexes are available. The output also shows "Using temporary; Using filesort", which means MySQL will create a temporary table to satisfy the "GROUP BY" clause.
Creating an index on "c2" alone will make no difference, and MySQL still needs to create a temporary table:
mysql> ALTER TABLE tb1 ADD KEY (c2);
mysql> EXPLAIN SELECT MAX(c1), c2 FROM tb1 WHERE c2 LIKE '%100' GROUP BY c1G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: tb1
partitions: NULL
type: ALL
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: 995789
filtered: 11.11
Extra: Using where; Using temporary; Using filesort
In this case, you can create a covered index on both "c1" and "c2" by adding the value of "c2" directly in the index to eliminate further data lookup.
mysql> ALTER TABLE tb1 ADD KEY covered(c1,c2);
mysql> EXPLAIN SELECT MAX(c1), c2 FROM tb1 WHERE c2 LIKE '%100' GROUP BY c1G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: tb1
partitions: NULL
type: index
possible_keys: covered
key: covered
key_len: 108
ref: NULL
rows: 995789
filtered: 11.11
Extra: Using where; Using index
As the EXPLAIN plan above shows, MySQL will now use the covered index and avoid creating a temporary table.
Combined index
A combined index consists of values from multiple columns and can be considered as an array of rows that are sorted by concatenating values of the indexed columns. This is can be useful in a GROUP BY statement.
mysql> EXPLAIN SELECT c1, c2 from tb1 WHERE c2 LIKE '%100' ORDER BY c1 DESC LIMIT 10G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: tb1
partitions: NULL
type: ALL
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: 995789
filtered: 11.11
Extra: Using where; Using filesort
MySQL performs a "filesort" operation which is somewhat slow, especially if it requires sorting a lot of rows. To optimize this query, you can create a combined index on both columns that are being sorted.
mysql> ALTER TABLE tb1 ADD KEY my_sort2 (c1, c2);
mysql> EXPLAIN SELECT c1, c2 from tb1 WHERE c2 LIKE '%100' ORDER BY c1 DESC LIMIT 10G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: tb1
partitions: NULL
type: index
possible_keys: NULL
key: my_sort2
key_len: 108
ref: NULL
rows: 10
filtered: 11.11
Extra: Using where; Using index
The EXPLAIN plan now shows that MySQL can use the combined index to avoid additional sorting since the index is already sorted.
Conclusion
Using EXPLAIN and different type of indexes can increase performance significantly. Having an index on the table doesn’t necessarily mean that MySQL can use it for your queries. Always be sure to validate your assumptions by using EXPLAIN and optimize your queries using indexes.
See also
Source: Azure Blog Feed