Optimizing MySQL at 30,000 feet
This is cool. I just made a key SQL query on my site go from 12+ second response time to .001 second response time, all while sitting on an Alaska Airlines flight using GoGo Wifi.
I have a web site that uses MySQL, and the database has a table that contains a 2 column primary key where each column is varchar(3), 2 text columns, a created_at and an updated_at column. The functionality of the site requires both insert and select on this table, and since I don’t need transactions I chose MyISAM per the MySQL documentation. The table acts like a cache of text data, where each row represents 130,000 characters of text divided into the two text columns. So, the table started empty, and each time someone on the website requests something that isn’t in the table, the data is created and inserted into the table (to avoid creating the data the next time it is created).
At first, it looked like everything was super fast. But then after a day or so it was REALLY slow. The table is up to >13,000 rows and the response time was > 12 seconds for each query.
Immediately, I noticed that if I do this query (col1 and col2 are the primary key of the table):
select * from mytable where col1=810 and col2=394
that my result took FOREVER.
However, if I did this:
select col1, col2 from mytable where col1=810 and col2=394
my result came back in .01 seconds. So I’m thinking hmm, it must be the text fields that’s making it slow. But then I did an explain:
mysql> explain select * from mytable where col1=810 and col2=394;
+----+-------------+-----------+------+---------------+------+---------+------+-------+-------------+| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-----------+------+---------------+------+---------+------+-------+-------------+
| 1 | SIMPLE | mytable | ALL | PRIMARY | NULL | NULL | NULL | 13911 | Using where |
+----+-------------+-----------+------+---------------+------+---------+------+-------+-------------+
1 row in set (0.00 sec)
This doesn’t look good, since it’s not using my index and it’s scanning every row in the table. Then it hit me – my primary key columns are VARCHAR, not INTEGER, but yet I was writing my query as if they were INTEGERs. Not being a MySQL expert, it didn’t occur to me that this would cause MySQL to do a scan of the entire table rather than using my PK. DUH!. So I changed it to this:
mysql> explain select * from mytable where col1='810' and col2='394';
+----+-------------+-----------+-------+---------------+---------+---------+-------------+------+-------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-----------+-------+---------------+---------+---------+-------------+------+-------+
| 1 | SIMPLE | mytable | const | PRIMARY | PRIMARY | 18 | const,const | 1 | |
+----+-------------+-----------+-------+---------------+---------+---------+-------------+------+-------+
What a difference! With this simple change, my select query went from taking >12 seconds on a 13k row table to taking .01 seconds. Voila, you learn something new every day.
