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

[grisha2217]

Deferred Join: A Deep Dive ​

Deferred join is powerful. Deferred join is simple. Deferred join is misunderstood.

hackmysql.com

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...

Read more

Continue reading...