MySQL slow query lookup and tuning tests

Edit the my.cnf or my.ini file to remove the comments from the following lines of code:

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

This allows slow queries and queries that do not use indexes to be logged.
After doing so, execute the tail-f command on the mysql-slow.log file, and you will see both slow queries for the records and queries with unused indexes.
Extract a random 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 |
+----+-------------+---------------------+------+---------------+------+---------+------+------+-------------+
Notice the rows and key columns above. rows shows how many rows are affected by the query, and we don't want this value to be too large. key shows which index is used, and NULL indicates that no index is used for the query.
If you want to make your queries faster, you may need to add indexes to certain columns:
CREATE INDEX myapp_mytable_myfield_idx on myapp_mytable(myfield);
In addition to configuring the mysql configuration file to record slow queries, there are several ways 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 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;