MySQL Quick Bulk Inserts

Question

We are developing the “Report Search” function for the client. One of the requirements is that they can view a specific result and see which search terms lead to it.

Our table search_resultsis just a mapping from searches.idto results.id.

So, we need to do a bulk insert into this table and know the fastest way to do this without seriously affecting the performance of the search query.

Search reports are often not required, so the table may be> 90% of the inserts. In addition, inserts are not required immediately, so a delay in storage is acceptable.

Our application is built on a standard LAMP stack.

We are open to suggestions on other methods of storing this data. Some of the other ideas that we have reviewed are as follows:

• Postpone inserts to a later stage using the cron job. Showing results in a file.
• A separate PHP process for insertion expires.

Answer 1

The following is a good overview of what you can do to speed up INSERTin MySQL 5.1: http://dev.mysql.com/doc/refman/5.1/en/insert-speed.html

Including

• Using multiple lists VALUES
• Function use INSERT DELAYED
• Parallel Inserts