Optimize Large MySQL Table Queries with Indexes 1

Posted by Ben Reubenstein Sat, 23 Aug 2008 03:59:00 GMT

I have been using the magic of Rails for a lot lately but some of those apps are starting to take off and get slow. It isn't Rails however it is MySQL queries to big tables. You don't have to be a MySQL rocket scientist to really make a huge difference. Want to improve queries to your big tables? Add indexes for the columns you use most in your where clause.

1. Turn on slow query logging in your my.cnf (most likely in /etc/mysql/my.cnf). Most like you will just have to uncomment this line:

log_slow_queries = /var/log/mysql/mysql-slow.log

2. Examine your logs and then use the mysql EXPLAIN query to see why it is performing slowly. This query needs to look at almost the ENTIRE table to find a result.
mysql> EXPLAIN select * from network_tests where user_id = 1;
+----+-------------+---------------+------+---------------+------+---------+------+--------+-------------+
| id | select_type | table         | type | possible_keys | key  | key_len | ref  | rows   | Extra       |
+----+-------------+---------------+------+---------------+------+---------+------+--------+-------------+
|  1 | SIMPLE      | network_tests | ALL  | NULL          | NULL | NULL    | NULL | 264559 | Using where | 
+----+-------------+---------------+------+---------------+------+---------+------+--------+-------------+
1 row in set (0.00 sec)

3. Add indexes to the columns that you are querying on most:

mysql> ALTER TABLE network_tests ADD INDEX(user_id,test_key);
Query OK, 285075 rows affected (1 min 7.14 sec)
Records: 285075  Duplicates: 0  Warnings: 0

4. Run explain again, see how many rows now need to be checked for this query? VERY NICE!.

mysql> EXPLAIN select * from network_tests where user_id = 1;
+----+-------------+---------------+------+---------------+---------+---------+-------+------+-------------+
| id | select_type | table         | type | possible_keys | key     | key_len | ref   | rows | Extra       |
+----+-------------+---------------+------+---------------+---------+---------+-------+------+-------------+
|  1 | SIMPLE      | network_tests | ref  | user_id       | user_id | 5       | const |  118 | Using where | 
+----+-------------+---------------+------+---------------+---------+---------+-------+------+-------------+
1 row in set (0.00 sec)