<?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++;
}
}?>
-
What is this code supposed to do?cairnz– cairnz2011-04-04 08:30:05 +00:00Commented Apr 4, 2011 at 8:30
-
I have two tables called master and master_break. master table contains fields called case_number and boxes.. I need to create master_break table using master values... this query consumes more time and memory. how to increase execution speedtharindu– tharindu2011-04-04 08:35:51 +00:00Commented Apr 4, 2011 at 8:35
Add a comment
|
5 Answers
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.
Comments
You can read about this in the manual - particularly, the stuff about insert....select, delayed, multiple row inserts, and disable/enable keys.
Comments
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)
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
Comments
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.