1

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();
}
Improve this question

3 Answers 3

Reset to default
2

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.

Improve this answer
Sign up to request clarification or add additional context in comments.

Comments

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
Improve this answer

2 Comments

Unfortunately, I cannot use MySQL above 5
The recursive solution is fine - it's comparable to the variable method I posted below (the recursive is just a bit slower: 8 sec compared to 6.5 sec on my computer). The second one however is very slow (1 min 18 sec).
1

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.

Improve this answer

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.