Solutions for Slow Queries

MySQL Tutorials - Herong's Tutorial Examples

∟Solutions for Slow Queries

This section provides some suggestions to resolve slow query problems including refreshing table statistics, rebuilding indexes, optimizing table data storage, and changing storage engine.

If you have a slow query that runs for more than 1 minute, you can follow some suggestions described below to improve its performance.

1. Use the "EXPLAIN", also called DESCRIBE, statement to see the query execution plan. If you see "ALL" as the query "type", MySQL will do a full table scan to complete the query, which will result a very poor performance. Possible values for "type" are "ALL, index, range, ref, eq_ref, const, system", sorted from the worst to the best.

---
mysql> explain select * from Profile where FirstName > 'bike';
+----+-------------+...+------+---------------+------+--------+----------+
| id | select_type |...| type | possible_keys | key  | rows   | filtered |
+----+-------------+...+------+---------------+------+--------+----------+
|  1 | SIMPLE      |...| ALL  | NULL          | NULL | 100000 |    33.33 |
+----+-------------+...+------+---------------+------+--------+----------+

2. User "ALTER TABLE ... ADD INDEX" statement to add index for columns used in the WHERE clause.

mysql> ALTER TABLE Profile ADD INDEX `Prof_IDX_FN` (`FirstName`);

mysql> explain select * from Profile where FirstName > 'bike';
+----+-------------+...+------+---------------+------+-------+----------+
| id | select_type |...| type | possible_keys | key  | rows  | filtered |
+----+-------------+...+------+---------------+------+-------+----------+
|  1 | SIMPLE      |...| ALL  | Prof_IDX_FN   | NULL | 10000 |    54.42 |
+----+-------------+...+------+---------------+------+-------+----------+

3. Use "ANALYZE" statement to refresh table statistics.

mysql> analyze table Profile;
+------------------+---------+----------+----------+
| Table            | Op      | Msg_type | Msg_text |
+------------------+---------+----------+----------+
| herongdb.profile | analyze | status   | OK       |
+------------------+---------+----------+----------+

4. Use "OPTIMIZE TABLE" statement to reorganize the physical storage of the table data and associated index data, to reduce storage space and improve I/O efficiency when accessing the table.

mysql> optimize table Profile;
+------------------+----------+----------+----------+
| Table            | Op       | Msg_type | Msg_text |
+------------------+----------+----------+----------+
| herongdb.profile | optimize | status   | OK       |
+------------------+----------+----------+----------+

5. Use "ALTER TABLE ... ENGINE=..." statement to change storage engine for the table. MyISAM will out-perform InnoDB on large tables because MyISAM locks down the entire table instead of individual rows. But obviously, InnoDB performs much better when multiple users want to access the same table.

mysql> SELECT TABLE_NAME, ENGINE FROM information_schema.TABLES
    -> WHERE TABLE_NAME = 'Profile';
+------------+--------+
| TABLE_NAME | ENGINE |
+------------+--------+
| Profile    | InnoDB |
+------------+--------+

mysql> ALTER TABLE Profile ENGINE=MyISAM