Recordset Iterating

Question

I want to iterate through records returned from a MySQL database using Perl, but only ten records at a time. The reason is that the server component can only handle 10 items per request.

For example:

If the query returned 35 records then I have to send the data in 4 requests:

Request #         # of Records
--------             --------
   1                    10
   2                    10
   3                    10
   4                     5

What is the best way to accomplish the task?

Answer 1

Look at the LIMIT clause for MySQL. You could have a query like:

SELECT * from some_table LIMIT 0, 10;
SELECT * from some_table LIMIT 10, 10;

etc. where the first number after LIMIT is the offset, and the second number is the number of records.

You'd of course first need to do a query for the total count and figure out how many times you'll need to run your select query to get all results.

Alternatively, in Perl you can use an ORM package like DBIx::Class that can handle pagination through sets of results and auto-retrieving them for you.

Answer 2

For first page:

SELECT  *
FROM    table1
ORDER BY
        field
LIMIT 0, 10

For seconds page:

SELECT  *
FROM    table1
ORDER BY
        field
LIMIT 10, 10

etc.

Answer 3

You can adjust the query to select 10 rows:

select * 
from yourtable
order by idcolumn
limit 10;

When iterating over the rows, store the ID of the row you process. After you've processed 10 rows, fetch the next 10:

select * 
from yourtable
where idcolumn > stored_id
order by idcolumn
limit 10;

Continue the last query until it returns less than 10 rows.