title: Fastest way to insert/update a million rows in Laravel 5.7
body: <p>I'm using Laravel 5.7 to fetch large amounts of data (around 500k rows) from an API server and insert it into a table (call it Table A) quite frequently (at least every six hours, 24/7) - however, it's enough to insert only the changes the next time we insert (but at least 60-70% of the items will change). So this table will quickly have tens of millions of rows.</p>
  
  <p>I came up with the idea to make a helper table (call it Table B) to store all the new data into it. Before inserting everything into Table A, I want to compare it to the previous data (with Laravel, PHP) from Table B - so I will only insert the records that need to be updated. Again it will usually be around 60-70% of the records.</p>
  
  <p><strong>My first question</strong> is if this above-mentioned way is the preferred way of doing it, in this situation (obviously I want to make it happen as fast as possible.) I assume that searching for an updating the records in the table would take a lot more time and it would keep the table busy / lock it. Is there a better way to achieve the same (meaning to update the records in the DB).</p>
  
  <hr>
  
  <p>The second issue I'm facing is the slow insert times. Right now I'm using a local environment (16GB RAM, I7-6920HQ CPU) and MySQL is inserting the rows very slowly (about 30-40 records at a time). The size of one row is around 50 bytes. </p>
  
  <p>I know it can be made a lot faster by fiddling around with InnoDB's settings. However, I'd also like to think that I can do something on Laravel's side to improve performance.</p>
  
  <p>Right now my Laravel code looks like this (only inserting 1 record at a time):</p>
  
  <pre><code>foreach ($response as $key =&gt; $value)
  {
      DB::table('table_a')
          -&gt;insert(
          [
              'test1' =&gt; $value['test1'],
              'test2' =&gt; $value['test2'],
              'test3' =&gt; $value['test3'],
              'test4' =&gt; $value['test4'],
              'test5' =&gt; $value['test5'],
          ]);
  }
  </code></pre>
  
  <p><code>$response</code> is a type of array.</p>
  
  <p><strong>So my second question:</strong> is there any way to increase the inserting time of the records to something like 50k/second - both on the Laravel application layer (by doing batch inserts) and MySQL InnoDB level (changing the config).</p>
  
  <p>Current InnoDB settings:</p>
  
  <pre><code>innodb_buffer_pool_size        = 256M
  innodb_log_file_size           = 256M
  innodb_thread_concurrency      = 16
  innodb_flush_log_at_trx_commit = 2
  innodb_flush_method            = normal
  innodb_use_native_aio = true
  </code></pre>
  
  <p>MySQL version is 5.7.21.</p>
  
  <p>If I forgot to tell/add anything, please let me know in a comment and I will do it quickly.</p>
  
  <p><strong>Edit 1:</strong>
  The server that I'm planning to use will have SSD on it - if that makes any difference. I assume MySQL inserts will still count as I/O.</p>