PHP how to run sql query one part at a time?

Question

I have a table with roughly 1 million rows. I'm doing a simple program that prints out one field from each row. However, when I started using mysql_pconnect and mysql_query the query would take a long time, I am assuming the query needs to finish before I can print out even the first row. Is there a way to process the data a bit at a time?

--Edited-- I am not looking to retrieve a small set of the data, I'm looking for a way to process the data a chunk at a time (say fetch 10 rows, print 10 rows, fetch 10 rows, print 10 rows etc etc) rather than wait for the query to retrieve 1 million rows (who knows how long) and then start the printing.

Answer 1

This is commonly needed for pagination. You can use the limit keyword in your select query. Search for limit here:

The LIMIT clause can be used to constrain the number of rows returned by the SELECT statement. LIMIT takes one or two numeric arguments, which must both be nonnegative integer constants (except when using prepared statements).

With two arguments, the first argument specifies the offset of the first row to return, and the second specifies the maximum number of rows to return. The offset of the initial row is 0 (not 1):

SELECT * FROM tbl LIMIT 5,10;  # Retrieve rows 6-15

To retrieve all rows from a certain offset up to the end of the result set, you can use some large number for the second parameter. This statement retrieves all rows from the 96th row to the last:

SELECT * FROM tbl LIMIT 95,18446744073709551615;

With one argument, the value specifies the number of rows to return from the beginning of the result set:

SELECT * FROM tbl LIMIT 5;     # Retrieve first 5 rows

In other words, LIMIT row_count is equivalent to LIMIT 0, row_count.

Answer 2

Do you really want to print one million fields?

The customary solution is to use some kind of output pagination in your web application, showing only part of the result. On SELECT queries you can use the LIMIT keyword to return only part of the data. This is basic SQL stuff, really. Example:

SELECT * FROM table WHERE (some conditions) LIMIT 40,20

shows 20 entries, starting from the 40th (off by one mistakes on my part may be possible).

It may be necessary to use ORDER BY along with LIMIT to prevent the ordering from randomly changing under your feet between requests.

Answer 3

Printing one million fields will take some time. Retrieving one million records will take some time. Time adds up.

Have you profiled your code? I'm not sure using limit would make such a drastic difference in this case.

Doing something like this

while ($row = mysql_fetch_object($res)) {
   echo $row->field."\n";
}

outputs one record at a time. It does not wait for the whole resultset to be returned.

If you are dealing with a browser you will need something more.

Such as this

ob_start();
$i = 0;
while ($row = mysql_fetch_object($res)) {
   echo $row->field."\n";
   if (($i++ % 1000) == 0) {
       ob_flush();
   }
}
ob_end_flush();

Answer 4

You might be able to use Mysqli::use_result

combined with a flush to output the data set to the browser. I know flush can be used to output data to the browser at an incremental state as I have used it before to do just that, however I am not sure if mysqli::use_result is the correct function to retrieve incomplete result sets.

Answer 5

This is how I do something like that in Oracle. I'm not sure how it would cross over:

declare
my_counter integer := 0;
begin
for cur in (
select id from table
) loop
  begin
    -- do whatever your trying to do
    update table set name = 'steve' where id = cur.id;
    my_counter := my_counter + 1;
    if my_counter > 500 then
      my_counter := 0;
      commit;
    end if;
    end;
  end loop;
  commit;
end;

Answer 6

An example using the basic mysql driver.

define( 'CHUNK_SIZE', 500 );

$result = mysql_query( 'select count(*) as num from `table`' );
$row = mysql_fetch_assoc( $result );

$totalRecords = (int)$row['num'];

$offsets = ceil( $totalRecords / CHUNK_SIZE );

for ( $i = 0; $i < $offsets; $i++ )
{
  $result = mysql_query( "select * from `table` limit " . CHUNK_SIZE . " offset " . ( $i * CHUNK_SIZE ) );
  while ( $row = mysql_fetch_assoc( $result ) )
  {
    // your per-row operations here
  }
  unset( $result, $row );
}

This will iterate over your entire row volume, but do so only 500 rows at a time to keep memory usage down.

Answer 7

It sounds like you're hitting the limits of various buffer sizes within the mysql server... Some methods you could do would be to specify the field you want in the SQL statement to reduce this buffer size, or play around with the various admin settings.

OR, you can use a pagination like method but have it output all on one page...

(pseudocode)

 function q($part) {
      $off = $part*SIZE_OF_PARTITIONS;
      $size = SIZE_OF_PARTITIONS;

      return( execute_and_return_sql('SELECT `field` FROM `table` LIMIT $off, $size'));
    }

    $ii = 0;

    while ($elements = q($ii)) {
      print_fields($elements);
      $ii++;
    }

Answer 8

Use mysql_unbuffered_query() or if using PDO make sure PDO::MYSQL_ATTR_USE_BUFFERED_QUERY is false.

Also see this similar question.

Edit: and as others have said, you may wish to combine this with flushing your output buffer after each batch of processing, depending on your circumstances.