title: import bulk data into MySQL
body: <p>So I'm trying to import some sales data into my MySQL database. The data is originally in the form of a raw CSV file, which my PHP application needs to first process, then save the processed sales data to the database.</p>
  
  <p>Initially I was doing individual <code>INSERT</code> queries, which I realized was incredibly inefficient (~6000 queries taking almost <strong>2 minutes</strong>). I then generated a single large query and <code>INSERT</code>ed the data all at once. That gave us a 3400% increase in efficiency, and reduced the query time to just over <strong>3 seconds</strong>.</p>
  
  <p>But as I understand it, <code>LOAD DATA INFILE</code> is supposed to be even quicker than any sort of <code>INSERT</code> query. So now I'm thinking about writing the processed data to a text file and using <code>LOAD DATA INFILE</code> to import it into the database. Is this the optimal way to insert large amounts of data to a database? Or am I going about this entirely the wrong way?</p>
  
  <p>I know a few thousand rows of mostly numeric data isn't a lot in the grand scheme of things, but I'm trying to make this intranet application as quick/responsive as possible. And I also want to make sure that this process scales up in case we decide to license the program to other companies.</p>
  
  <p><strong>UPDATE:</strong></p>
  
  <p>So I did go ahead and test <code>LOAD DATA INFILE</code> out as suggested, thinking it might give me only marginal speed increases (since I was now writing the same data to disk twice), but I was surprised when it cut the query time from over 3300ms down to ~240ms. The page still takes about ~1500ms to execute total, but it's still noticeably better than before.</p>
  
  <p>From here I guess I'll check to see if I have any superfluous indexes in the database, and, since all but two of my tables are InnoDB, I will look into optimizing the InnoDB buffer pool to optimize the overall performance.</p>