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

• Performance best practices for using Azure Database for PostgreSQL