Searching and tuning MySQL slow queries

From: http://parand.com/say/index.php/2009/09/01/finding-and-fixing-slow-mysql-queries/bar.html

Edit the my. CNF or my. ini file and remove the comments of the following lines of code:

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

This will record slow queries and queries without indexes.

After doing so, execute the tail-F command on the mysql-slow.log file and you will be able to see slow queries that record them and queries that do not use indexes.

Extract A Slow query and execute explain:

explain low_query

You will see the following results:

+----+-------------+---------------------+------+---------------+------+---------+------+------+-------------+

| 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 |

+----+-------------+---------------------+------+---------------+------+---------+------+------+-------------+

Note the preceding rows and key columns. Rows shows the number of rows affected by this query. We do not want to make this value too large. Key indicates which index is used. If it is null, no index is used for the query.

To make the query faster, you may need to add an index for some columns:

CREATE INDEX myapp_mytable_myfield_idx on myapp_mytable(myfield);

In addition to configuring the MySQL configuration file to achieve slow query of records, the following methods can be used to record slow queries:

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 sINNER JOIN INFORMATION_SCHEMA.TABLES t ON s.TABLE_SCHEMA = t.TABLE_SCHEMA AND s.TABLE_NAME = t.TABLE_NAMEINNER JOIN (SELECT TABLE_SCHEMA, TABLE_NAME, INDEX_NAME, MAX(SEQ_IN_INDEX) AS max_columnsFROM INFORMATION_SCHEMA.STATISTICSWHERE 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_NAMEWHERE 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;