In the past, I’ve worked with a large table for ‘transactions’ (with way over 800 million rows) – I’ve found that things tend to break on large volumes of clients trying to things such as INSERT (the disk keeps doing weird stuff like doing an earlier INSERT before an older one, leading to memory problems and bottlenecks).

I learned a small trick, and that is to use INSERT DELAYED

When a client uses INSERT DELAYED, it gets an okay from the server at once, and the row is queued to be inserted when the table is not in use by any other thread.

By using this, it will not slow the database down by much, but it will free the database to write the INSERTs to disk at any time, so it can bundle a bunch of inserts together – this will stop any weird behavior in your MySQL database, in turn making it faster than it was previously (no memory problems, having to restart all the time and no more large bottlenecks).

Another thing to watch out is using MySQLs time functions like, CURRENT_TIMESTAMP or CUR_DATE() because they will be called everytime the query is executed so you should make sure any time data is generated by your programming language (like PHP) rather than by the database.

This sort of practice would be used for something that records/logs all transactions in a virtual economy, or logging all user events etc.

Tags: INSERT DELAYED, large volume, mysql, PHP, script, webapp