MySQL DELAY_KEY_WRITE Option

Another performance option in MySQL is the DELAY_KEY_WRITE option. According to the MySQL documentation the option makes index updates faster because they are not flushed to disk until the table is closed.

Note that this option applies only to MyISAM tables,

You can enable it on a table by table basis using the following SQL statement:

ALTER TABLE sometable DELAY_KEY_WRITE = 1;

This can also be set in the advanced table options in the MySQL Query Browser.

This performance option could be handy if you have to do a lot of update, because you can delay writing the indexes until tables are closed. So frequent updates to large tables, may want to check out this option.

Ok, so when does MySQL close tables?

That should have been your next question. It looks as though tables are opened when they are needed, but then added to the table cache. This cache can be flushed manually with FLUSH TABLES; but here's how they are closed automatically according to the docs:

• When the cache is full and a thread tries to open a table that is not in the cache.
• When the cache contains more than table_cache entries and a thread is no longer using a table.
• FLUSH TABLES; is called.

If DELAY_KEY_WRITE is enabled, this means that the key buffer for tables with this option are not flushed on every index update, but only when a table is closed. This speeds up writes on keys a lot, but if you use this feature, you should add automatic checking of all MyISAM tables by starting the server with the --myisam-recover option (for example, --myisam-recover=BACKUP,FORCE).

So if you do use this option you may want to flush your table cache periodically, and make sure you startup using the myisam-recover option.