In MySQL, what's the most efficient way to query an entire table in chunks? If I have a table User is it faster to do something like:

SELECT * FROM user WHERE id &gt; ? ORDER BY id LIMIT 10000

(With each subsequent query using the previous last row's id as the param)

Or to use a LIMIT with an offset, like this:

SELECT * FROM user ORDER BY id LIMIT ?, 10000

(With total number of records retrieved so far being the offset).

As an additional complication, what if I wanted to perform a similar query based on a linking table join? Like say a user could belong to multiple roles, so I wanted a query like

SELECT u.*
 FROM user u
 INNER JOIN user_roles ur ON ur.user_id = u.id
 WHERE ur.role_name IN ('Admin', 'SuperUser', 'etc.')
 LIMIT ?, 10000


Does a join affect the decision as to which style query is preferred?
OFFSET is very bad.  Instead of processing the data in Order(N) time, it will take Order(N*N).  This is because of the effort to skip past the "offset" rows.

Instead "remember where you left off".  I go into details about such in these two blogs:


Pagination via OFFSET is bad:  http://mysql.rjweb.org/doc.php/pagination (It gives details on "left off" as a good alternative)
How to delete lots of rows via chunking:  http://mysql.rjweb.org/doc.php/deletebig . You need to adapt it from DELETE to what you are doing.  It also gives tips on how to walk through the PRIMARY KEY to efficiently discover the next 'chunk'.


These tips apply to all versions of MySQL.