500 Internal Server Error - Fetching 200,000 rows


#1

Hello,

I’m trying to build an XML product feed dynamically using PHP and MySQL and consisting of 200,000 products.

When calling 1 large MySQL query of all items, I get the “Memory Exhausted” error. So I broke up the MySQL queries into smaller batches which would use less memory:

[php]
$sql = “SELECT COUNT(*) as num FROM items”;
$result = $mysqli->query($sql);
$obj = $result->fetch_object();
$itemcount = $obj->num; // 200,000 total items

$runs = $itemcount / 2500; // Around 80 smaller queries
for ($i = 0; $i <= $runs; $i++) {
$offset = $i * 2500;
$sql = “SELECT id,title,price,description FROM items LIMIT 2500,$offset”;
$result = $mysqli->query($sql);
while ($obj = $result->fetch_object()) {
// testing - do nothing
}
echo “Run: “.$i.”
”;
}
[/php]

Unfortunately, I get a 500 Internal Server Error after about 30 seconds. However, I am not getting the “Memory Exhausted” error. It works fine if I set the total number of items at 50,000 though, but 200,000 seems to be too much.

Does anyone know what’s going on? Fetching 200,000 rows doesn’t seem like a tremendous MySQL task, considering this script above is just a test where nothing is being done except fetching the items.

Thanks


#2

Nevermind, I solved it by switching the two MySQL LIMIT numbers around for the offset starting point and batch size. This seems to have fixed the problem.

Thanks for looking.
Kind regards