Optimized List Queries by Xon

Compatible XF 2.x versions

Visible branding: No

This add-on is aimed at large forum with tens of thousands to hundreds of thousands or more threads in a single forum. Smaller forum do not gain much benefit from this add-on.

Member List/Forum/Conversation/Reactions/XFMG lists:

MySQL/MariaDB implements 'early row lookup', when combined with LIMIT & OFFSET with a large offset, results in the large select statement pulling in more data than is required. Maybe one day MySQL/MariaDB will fix this. Maybe.

This causes an issue as this how conversations and threads implement paging.

This addon provides some optimized query for getting threads in a forum with large number of threads, or for conversations with a very high page count.

Ideally conversations would implement the position system like posts do, which would be even faster than using sub-selects to force 'late row lookup'.

For more information see:

Why does MYSQL higher LIMIT offset slow the query down?

Scenario in short: A table with more than 16 million records [2GB in size]. The higher LIMIT offset with SELECT, the slower the query becomes, when using ORDER BY *primary_key* So SELECT * ...

stackoverflow.com
MySQL ORDER BY / LIMIT performance: late row lookups

A late row lookup requires that the engine looks up a row in a table only if it had not been filtered by the index. MySQL is not capable of doing this but this can be emulated. It improves the LIMIT performance and filtering efficiency on the indexed fields

explainextended.com