How to speed up query execution MySQL

Question

You must fill out the form table 1 to 1,000,000:

Table view
CREATE TABLE `test` ( `id` INT UNSIGNED NOT NULL AUTO_INCREMENT , `value` INT UNSIGNED NOT NULL , PRIMARY KEY (`id`)) ENGINE = InnoDB;"

I wrote a function to do this, but adding data to the table is too slow, how can I improve the performance of insert data?

function InsertData(){

    global $MySQL;
    for($i = 1; $i != 1000000; $i++){
        $MySQL->query("INSERT INTO `name` (`id`, `value`) VALUES ($i, $i);");
    }

    $MySQL->close();
}

Answer 1

You could use Transactions in order to only commit once every thousands inserts (or, if you are brave, after the million of queries). Here is the (brave) example:

function InsertData(){
   global $MySQL;

   // Start transactions
   $MySQL->query('SET autocommit=0;');
   $MySQL->query('START TRANSACTION;');

   for($i = 1; $i != 1000000; $i++){
      $MySQL->query("INSERT INTO `name` (`id`, `value`) VALUES ($i, $i);");
   }
   // So far, nothing as actually been saved to database
  
   // Commit all inserts.
   $MySQL->query('COMMIT;');
   $MySQL->query('SET autocommit=1;');

   $MySQL->close();
}

If this is too much for one single transactions due to some MySQL limit, you could perform the Commit every 10.000 inserts or so:

function InsertData(){
   global $MySQL;

   // Start transactions
   $MySQL->query('SET autocommit=0;');
   $MySQL->query('START TRANSACTION;');

   for($i = 1; $i != 1000000; $i++){
      $MySQL->query("INSERT INTO `name` (`id`, `value`) VALUES ($i, $i);");
      if($i % 10000 == 0) {
         $MySQL->query('COMMIT;');
         $MySQL->query('START TRANSACTION;');
      }
   }
   // So far, nothing as actually been saved to database
  
   // Commit all inserts.
   $MySQL->query('COMMIT;');
   $MySQL->query('SET autocommit=1;');

   $MySQL->close();
}

Pay attention to the eventual limit -> https://stackoverflow.com/a/2298325/2814721

And, of course, this is intended to be an experiment or one-shot script. Not advised to do in a production database.