If I have a table
User is it faster to do something like:
SELECT * FROM user WHERE id > ? 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
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?