Skip to content

Instantly share code, notes, and snippets.

@aliaspooryorik

aliaspooryorik/window_function_paging.sql

Created August 29, 2018 10:57
Show Gist options
  • Star 0 You must be signed in to star a gist
  • Fork 0 You must be signed in to fork a gist
  • Save aliaspooryorik/25e4b18680d1fb6c7861d73100a247d8 to your computer and use it in GitHub Desktop.
Save aliaspooryorik/25e4b18680d1fb6c7861d73100a247d8 to your computer and use it in GitHub Desktop.
Download ZIP
efficient MYSQL pagination with window functions
Raw
window_function_paging.sql
SELECT *
FROM (
SELECT uuid,
row_number() over (ORDER BY uuid) AS rn,
count(*) over () AS totalrows
FROM tblfoo
) AS tmp
WHERE rn BETWEEN 212201 AND 212210
ORDER BY uuid
;
-- LIMIT gets slower the greater the offset as it gets the records then discards them
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment