CODEWITHSUNDEEP
phpmysqlloopsbatch-processing
Shaonline
Shaonline

Reputation: 1637

How to perform batch operation using mysql and php

I am trying to insert multiple records using single MySQL query, but i don't want to insert a huge number of records at once.

The following code fetches, buildes and inserts the records

if(is_array($id_rec)){
        $values = "";
        foreach($id_rec as $key=>$value){
            $values .= "(".(int)$value.",".(int)$id_group."), ";
        }
        $values = rtrim($values,", ");
        $sql = "INSERT IGNORE INTO fu_rec_group_link (id_rec, id_group) VALUES ".$values;
        $GLOBALS['db']->execute($sql);

I have two questions here.

  1. Frist Question: How many records should i insert at once? What is right amount?
  2. Second Question: How can i pause/break the loop after reaching the max limit of records and insert it and then continue from i left?

Any help would be greatly appreciated.

Upvotes: 0

Views: 712

Answers (1)

Lloyd Banks
Lloyd Banks

Reputation: 36659

You should insert as many records as possible in a single INSERT as opposed to breaking it down into many INSERTS.

For example, doing 

INSERT INTO mytable (column1, column2, column3)
VALUES ('text', 'text', 'text'),
       ('text', 'text', 'text'),
       ('text', 'text', 'text'),
       ('text', 'text', 'text');

is faster than doing 

INSERT INTO mytable (column1, column2, column3)
VALUES ('text', 'text', 'text'),
       ('text', 'text', 'text');
INSERT INTO mytable (column1, column2, column3)
VALUES ('text', 'text', 'text'),
       ('text', 'text', 'text');

The aggregate difference in performance is more pronounced as the number of rows go up.

Upvotes: 1

Related Questions