2

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

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?

6

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:

These tips apply to all versions of MySQL.

  • 1
    Thanks! I didn't realize the LIMIT clause with an offset would result in a re-read through all previous rows for each iteration. – Jordan0Day Jan 9 '17 at 14:55

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.