When querying large datasets, avoid the script timeout
I have the following code:
$query = mysql_query("SELECT * FROM mytable");
while($row = mysql_fetch_assoc($query)){
mysql_query("INSERT INTO mytable2 (col1, col2)
VALUES ('".$row['val1']."', '".$row['val2']."')");
}
It is clear that the script expires at about 150,000 requests ... outside of the script memory increase what's the best way to prevent timeouts?
+3
source to share
2 answers
Why not run it as one request ???
$SQL = "INSERT INTO mytable2 (col1,col2) SELECT val1,val2 FROM mytable";
$query = mysql_query($SQL);
ALTERNATIVE
You can also throttle your INSERTS 200 at a time
$query = mysql_query("SELECT * FROM mytable");
$commit_count = 0;
$commit_limit = 200;
$comma = "";
$SQL = "INSERT INTO mytable2 (col1, col2) VALUES ";
while($row = mysql_fetch_assoc($query)){
$SQL .= $comma . "('".$row['val1']."','".$row['val2']."')";
$comma = ",";
$commit_count++;
if ( $commit_count == $commit_limit )
{
mysql_query($SQL);
$SQL = "INSERT INTO mytable2 (col1, col2) VALUES ";
$commit_count = 0;
$comma = "";
}
}
if ( $commit_count > 0 ) { mysql_query($SQL); }
You can change $commit_limit
to any positive number that is reasonable.
+6
source to share