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.

Answer 2

How about moving the logic to the database, using a recursive CTE?

insert into name (id, value)
with recursive cte as (
    select 1 id
    union all select id + 1 from cte where i < 1000000
)
select id, id from cte

That may be to many rows to generate at once with recursion. An alternative is to generate just 10 rows, then multiply the rows:

insert into name (id, value)
with recursive cte as (
    select 0 id
    union all select id + 1 from cte where i < 9
)
select id, id 
from (
    select 1 + c0.id + c1.id * 10 + c2.id * 100 + c3.id * 1000 + c4.id * 10000 + c5.id * 100000 id
    cte c0
    cross join cte c1
    cross join cte c2
    cross join cte c3
    cross join cte c4
    cross join cte c5
) t

Answer 3

Try this query:

INSERT INTO `test` (`id`, `value`) 
SELECT @row := @row + 1 AS row, @row 
FROM (SELECT 0 UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) AS t1,
     (SELECT 0 UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) AS t2, 
     (SELECT 0 UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) AS t3, 
     (SELECT 0 UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) AS t4, 
     (SELECT 0 UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) AS t5, 
     (SELECT 0 UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) AS t6, 
     (SELECT @row:=0) AS nums;

It's a "INSERT INTO... SELECT..." type of statement in which the SELECT statement itself is generating a million rows filled with pairs (1,1), (2,2), etc. Here is how it works:

1. The tables t1, t2, t3, t4, t5, t6 are 10 rows each. Cross joining them generates 10^6 = 1000000 combinations, so the resulting table will be with million rows;
2. For each of those rows, we SELECT the @row variable twice. And not only that but we also increment it with 1;
3. The nums table is used only to initialize the variable to 0 at the very beginning;
4. The resulting table is passed to the INSERT statement and the data is stored in the table.

A cleaner looking solution is to use recursive CTE with newer MySQL/MariaDB. It's the one that was submitted by user GMB:

INSERT INTO test (id, value)
WITH RECURSIVE temp AS (
    SELECT 1 AS row
    UNION SELECT row + 1 
    FROM temp
    WHERE row < 1000000
)
SELECT row, row
FROM temp;

Based on my tests, it was a bit slower. I didn't monitor memory usage.