Pagination via OFFSET

When adding a LIMIT to a UNION, this is the pattern to use:

( SELECT ... ORDER BY x  LIMIT 10 )
UNION
( SELECT ... ORDER BY x  LIMIT 10 )
ORDER BY x  LIMIT 10

Since you cannot predict which SELECT(s) will the “10” will come from, you need to get 10 from each, then further whittle down the list, repeating both the ORDER BY and LIMIT.

For the 4th page of 10 items, this pattern is needed:

( SELECT ... ORDER BY x  LIMIT 40 )
UNION
( SELECT ... ORDER BY x  LIMIT 40 )
ORDER BY x  LIMIT 30, 10

That is, collect 4 page’s worth in each SELECT, then do the OFFSET in the UNION.