CODEWITHSUNDEEP
phpmysqlsql
tharindu
tharindu

Reputation: 21

how to improve sql INSERT INTO speed

<?phph
$Q=mysql_query("SELECT case_number,boxes FROM master");
    while($arr=mysql_fetch_array($Q))

    {

             $s=trim($arr['case_number']);

             $noc=trim($arr['boxes']) ;

             $e=($s+$noc-1);


        for($x=$s;$s<=$e;$s++)
            {


                    mysql_query("INSERT INTO master_break (case_number) VALUES('$s')");

                    $x++;




        }

        }?>

Upvotes: 2

Views: 343

Answers (5)

symcbean
symcbean

Reputation: 48357

You can read about this in the manual - particularly, the stuff about insert....select, delayed, multiple row inserts, and disable/enable keys.

Upvotes: 2

user834912
user834912

Reputation:

Use prepared statements and stuff them all into a transaction. i.e.:

$pdoHandler->beginTransaction();
for (whatever) {
    $query = $pdoHandler->prepare(whatever);
    $query->bindParam(whatever);
    $query->execute();
}
try {
    $pdoHandler->commit();
} catch(PDOException $e) {
    do whatever;
}

That way, you'll be safe from SQL injections too.

Upvotes: 0

Haim Evgi
Haim Evgi

Reputation: 125486

make all your insert in one transaction speed up time significantly.

like

mysql_query('START TRANSACTION', $link); 
for ($i = 0; $i < 20; ++$i) 
{ 
   mysql_query("INSERT INTO master_break (case_number) VALUES('$s')", $link);
}
mysql_query('COMMIT', $link);

see in http://www.slideshare.net/jwage/doctrine-2-not-the-same-old-php-orm slide 52

Upvotes: 0

Frank Schmitt
Frank Schmitt

Reputation: 30775

Insert into master_break(case_number) 
(select case_number from master)

If you need only the unique ones, add a DISTINCT:

   Insert into master_break(case_number) 
    (select distinct case_number from master)

Upvotes: 1

Andr&#233; Moruj&#227;o
Andr&#233; Moruj&#227;o

Reputation: 7133

One thing you can try to do is inserting in batches. Insert syntax supports something like:

INSERT INTO master_break (case_number) VALUES (123), (456), ...

Try it with increasingly higher batch sizes until you find something that suits you.

Upvotes: 3

Related Questions