CODEWITHSUNDEEP
phpsqlmysqlperformance
Mathew Byrne
Mathew Byrne

Reputation: 3759

MySQL Quick Bulk Inserts

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:

Upvotes: 3

Views: 4879

Answers (1)

Jordan S. Jones
Jordan S. Jones

Reputation: 13903

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"

Upvotes: 6

Related Questions