Use "Deferred Join" for pagination (5x faster)

G

grisha2217

Active member

Efficient MySQL pagination using deferred joins - Aaron Francis

A slight modification to traditional offset/limit pagination that can drastically boost performance.
aaronfrancis.com
Simple Query
SQL: 
select * from t order by x limit 500, 20

Optimized Query
SQL: 
select * from t join (
  select pk from t order by x limit 500, 20
) dt using(pk)

I have tested it on my production server (6KK threads)
I had run query to fetch 1000 page (?page=1000) in node contains 450 000 threads
Before optimization (LIMIT 30 OFFSET 29970): 7 seconds
After optimization: 1.8 seconds.
 
Last edited:
Upvote 16
Back
Top Bottom