Super-fast database writes with INSERT DELAYED

Many Web sites write data to a database. Usually, the data absolutely must be properly saved, so the default way of adding records (using an SQL “INSERT” statement) ensures that the data is permanently stored on the server’s disks. Doing that takes a relatively long time in computer terms — it’s much slower than most things computers do.

In some cases, you might be storing data that’s not quite so important. And if it means your application can run much faster, you might be willing to risk a very small chance of data loss. That’s where MySQL’s “INSERT DELAYED” statement, which works with MyISAM table types (but not InnoDB tables), can be useful. (Tables are created as type MyISAM by default, so most tables are eligible to benefit from this tip.)

Adding the word “DELAYED” to your statement tells MySQL to remember the data to be added and return immediately to your application. MySQL will then write the data as soon as the database isn’t busy. This lets your insertion happen (effectively) immediately, and reduces the load on the database (and server). Using this technique can give your application a huge performance gain.

Since the record is not written immediately, there is a very small chance that the data will be lost before it’s written to the disk. However, the odds of this happening are very small. It would only happen if MySQL crashed before it had a moment of idle time to write out the record, if the server lost power, or if some similar unexpected event happened.

Using “INSERT DELAYED” is recommended for applications that do not absolutely depend upon records being immediately written. Any data which is only referred to at a later time (rather than on-demand) or in a summary fashion is a good candidate for using “INSERT DELAYED”. Some examples are logging the IP address of each visitor, or tracking ad impressions for each page viewed on your Web site. Be sure to read the official documentation for full details and additional considerations.

If you want to use “INSERT DELAYED”, first check the documentation to see if your application already supports it. If it does not, you’d need to modify the application. If you have any questions about whether a particular database table is a good candidate for using “INSERT DELAYED”, just ask us and we’ll take a look.