PHP MySQL running query in batches

The best way to do this depends on a couple of different things. Most importantly is when and why you are creating this XML file.

If you are creating the XML file on demand, and a user is waiting for the file then you'll need to do some fine tuning and testing for performance.

If it's something that's created on a regular basis, maybe a nightly or hourly task, and then the XML file is requested after it's built (something like an RSS feed builder) then if what you have works I would recommend not messing with it.

As far as performance, there are different things that can help. Put in some simple timers into your scripts and play with the number of records per batch and see if there is any performance differences.

$start = microtime(true);
//process batch
$end = microtime(true);
$runTimeMilliseconds = $end - $start;

If the issue is user feedback, you may consider using AJAX to kick off each batch and report progress to the user. If you give the user feedback, they'll usually be happy to wait longer than if they're just waiting on the page to refresh in whole.

Also, check your SQL query to make sure there's no hidden performance penalties there. http://dev.mysql.com/doc/refman/5.0/en/explain.html EXPLAIN can show you how MySQL goes about processing your queries.

At an extreme, I'd imagine the best performance could be accomplished through parallel processing. I haven't worked with it in PHP, but here's the primary reference http://www.php.net/manual/en/refs.fileprocess.process.php

Depending on your hosting environment you could find the total number of records and split it among sub processes. Each building their own XML fragments. Then you could combine the fragments. So process 1 may handle records 0 to 99, process 2 100 to 199, etc.

The best way to go about this is to schedule it as a CRON job, which i think is the best solution for batch processing in PHP. check this link for more info! Batch Processing in PHP. Hope this helps.

You would be surprised ONE simple select all without limit is the fastest,
because it only query database once,
everything else is processed locally

$sql = select all_columns from table;

<?php
// set a very high memory

// query without limit, if can avoid sorting is the best

// iterate mysql result, and set it to an array
// $results[] = $row
// free mysql_result

// write xml for every one thousand
// because building xml is consuming MOST memory
for ($i=0; $i<$len; ++$i)
{
  $arr = $results[$i];
  // do any xml preparation

  // dun forget file-write is expensive too
  if ($i%1000 == 0 && $i > 0)
  {
    // write to file
  }
}
?>