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

select * from t order by x limit 500, 20

Optimized Query

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.

 

[avalanch]

Upvoted

 

[Xon]

I've long since implemented this in a free add-on:

X

Optimized List Queries by Xon

Dec 21, 2018

A collection of replacement queries for getting lists of stuff aimed at large forums.

• Xon
• conversations forum performance
• Add-ons [2.x]