MySQL Quick Bulk Inserts
mysql, performance, php, sql
Solution
The following is a good overview on what you can do to speed up `INSERT`s on MySQL 5.1: http://dev.mysql.com/doc/refman/5.1/en/insert-speed.html
Including
- Using multiple `VALUES` lists
- Leveraging the `INSERT DELAYED` feature
- "Concurrent Inserts"
Problem
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.