≡ Menu

How to optimize MySQL MyISAM SELECT queries?

The question is – I have a MyISAM MySQL table and I have a lot of INSERTs and UPDATEs going on. This reduces the speed of my SELECT queries because of table level locking. Is there a way to optimize SELECT statements for MyISAM?

MyISAM is the default database engine for MySQL. The drawback of MyISAM is that it involves table level locking. So if you have lots of UPDATEs and INSERTs going on, the whole table will be locked for the duration of the statements. Compare this to the other MySQL engine InnoDB in which the locking happens at the row level. So an obvious solution to optimize SELECT statements is to switch to InnoDB. But note that some users have noted that SELECT statements are faster on MyISAM as compared to InnoDB.

There is a much simpler solution that has worked like miracles for me. This is to prioritize SELECT statements above UPDATEs.

First, locate your my.cnf file. As this article explains, this can be done using the following statement:

mysqld –help –verbose

So your my.cnf is located in some directory like /etc/mysql/my.cnf.

Open the my.cnf file. And then, add the following line to my.cnf.

low-priority-updates=1

What this does is that if, in the queue, there are several UPDATEs and SELECTs waiting for MySQL, the SELECT statements get the higher priority. This is in line with your problem.

From the MySQL reference guide:

If set to 1, all INSERTUPDATEDELETE, and LOCK TABLE WRITE statements wait until there is no pending SELECTor LOCK TABLE READ on the affected table. This affects only storage engines that use only table-level locking.

When I made this change, I could see about 10 times speed increase of my pages that just did rendering of HTML elements using only SELECT statements!

After you make this change, make sure that you test your my.cnf file using this article. Then, you should check if the low priority updates and inserts have actually been loaded by MySQL. First restart MySQL:

service mysqld restart

Then, use this article to check the values of all MySQL variables and options:

mysqladmin -u databaseuser -p variables

Thats it!. Everything is now setup for low priority updates on your MyISAM MySQL database engine. Report your findings by leaving a comment!

Comments on this entry are closed.