Finding and Fixing Slow MySQL Queries

Posted September 1, 2009 * Comments(1)

Notes to self, for future reference.

Edit /etc/mysql/my.cnf . Uncomment the following lines:

log_slow_queries        = /var/log/mysql/mysql-slow.log
long_query_time = 2
log-queries-not-using-indexes

This enables logging of slow queries and queries not making use of indexes.

Now tail -f the mysql-slow.log file. You’ll see the slow and non-index-using queries.

Grab a query that you’d like to examine. Open a mysql shell and run “explain” on it:

explain your_query_here;

You’ll see output that looks like:

+----+-------------+---------------------+------+---------------+------+---------+------+------+-------------+
| id | select_type | table               | type | possible_keys | key  | key_len | ref  | rows | Extra       |
+----+-------------+---------------------+------+---------------+------+---------+------+------+-------------+
|  1 | SIMPLE      | some_table          | ALL  | NULL          | NULL | NULL    | NULL |  166 | Using where |
+----+-------------+---------------------+------+---------------+------+---------+------+------+-------------+

Look at the rows and key fields in particular. rows shows how many rows were examined for query – you don’t want to see a high number. If you’re not using an index you may be doing a full-table-scan, examining every single row to find the value you’re looking for.

key shows which index, if any, was used. If you see NULL then no index was used.

To make the query go faster you may need an index. Look at the query and see what your’e selecting based on, and then create the corresponding index. For example, if we’re doing the selection based on the “myfield” field, you could create the index:

CREATE INDEX myapp_mytable_myfield_idx on myapp_mytable(myfield);

Once you create the index you should see that query no longer appearing in the slow query log.

Rinse and repeat for other queries.

1 Comment so far

  1. Kuroki Kaze on December 2nd, 2009

    You can also find tables with non-efficient (low cardinality) indexes by running:

    SELECT t.TABLE_SCHEMA AS `db`,
    t.TABLE_NAME AS `table`,
    s.INDEX_NAME AS `index name`,
    s.COLUMN_NAME AS `FIELD name`,
    s.SEQ_IN_INDEX `seq IN index`,
    s2.max_columns AS `# cols`,
    s.CARDINALITY AS `card`,
    t.TABLE_ROWS AS `est rows`,
    ROUND(((s.CARDINALITY / IFNULL(t.TABLE_ROWS, 0.01)) * 100), 2) AS `sel %`

    FROM INFORMATION_SCHEMA.STATISTICS s
    INNER JOIN INFORMATION_SCHEMA.TABLES t ON s.TABLE_SCHEMA = t.TABLE_SCHEMA AND s.TABLE_NAME = t.TABLE_NAME
    INNER JOIN (
    SELECT TABLE_SCHEMA, TABLE_NAME, INDEX_NAME, MAX(SEQ_IN_INDEX) AS max_columns
    FROM INFORMATION_SCHEMA.STATISTICS
    WHERE TABLE_SCHEMA != 'mysql' GROUP BY TABLE_SCHEMA, TABLE_NAME, INDEX_NAME ) AS s2 ON s.TABLE_SCHEMA = s2.TABLE_SCHEMA AND s.TABLE_NAME = s2.TABLE_NAME AND s.INDEX_NAME = s2.INDEX_NAME

    WHERE t.TABLE_SCHEMA != 'mysql' /* Filter out the mysql system DB */
    AND t.TABLE_ROWS > 10 /* Only tables with some rows */
    AND s.CARDINALITY IS NOT NULL /* Need at least one non-NULL value in the field */
    AND (s.CARDINALITY / IFNULL(t.TABLE_ROWS, 0.01)) < 1.00 /* unique indexes are perfect anyway */

    ORDER BY `sel %`, s.TABLE_SCHEMA, s.TABLE_NAME /* DESC for best non-unique indexes */
    LIMIT 10;

Leave a Reply