MySQL Quick Bulk Inserts

Question

We're developing a "Search Reporting" feature for a client. One of the requirements is that they can view a particular result and see which search terms lead to it.

Our search_results table is simply a mapping of searches.id to results.id.

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

The search reports are not required frequently, so the table likely be > 90% inserts. Also, the inserts are not required immediately, so deferring the storage is acceptable.

Our application is built on a standard LAMP stack.

We're open to suggestions for other methods for storing this data. Some other ideas we've looked at are:

• Deferring the inserts to a later stage using a cron job. Writing the results to a file in the meantime.
• Spawning a separate PHP process to do the inserts.

Answer 1

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

Including

1. Using multiple VALUES lists
2. Leveraging the INSERT DELAYED feature
3. "Concurrent Inserts"