Use "Deferred Join" for pagination (5x faster)
- Автор xenforo2
- RSS Feed XenForo
- 0 Ответы
Deferred Join: A Deep Dive
Deferred join is powerful. Deferred join is simple. Deferred join is misunderstood.
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, 20Optimized 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 pages
Before optimization (LIMIT 30 OFFSET 29970): 7 seconds...
Read more
Читать далее...