Overview Version (2) Discussion (2)

• 

  Optimized List Queries by Xon

  This add-on is aimed at large forum with tens of thousands to hundreds of thousands or more threads

  forum

  optimized

  optimized list queries
  About this resource

  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:
  

  • http://stackoverflow.com/questions/4481388/why-does-mysql-higher-limit-offset-slow-the-query-down
  • http://explainextended.com/2009/10/23/mysql-order-by-limit-performance-late-row-lookups/

  Configuration
  Under "Performance":
  

  
• Version

  2.13.0 Stable Available Feb 9, 2026 · 1

  Download

  File size: 28.1 KB

  SHA-256: da3f250e69641a2278fe6901fba17d91a7cba65721811a43dcf5799bd1f20819

  SHA-256 verified Package valid Compatibility checked Last verified: Wednesday at 12:10 AM

  • Require StandardLib v1.23.0+
  • Only post sort usage patch on old enough XF version
  • Improve XFMG v2.3+ compatibility

  2.12.0 Stable Available Dec 16, 2025 · 0

  Download

  File size: 27.8 KB

  SHA-256: 32649b15a30cd8d641bef5256502d25b30f3d169a80f2a54cf133f5e49f51119

  SHA-256 verified Package valid Compatibility checked Last verified: Wednesday at 12:10 AM

  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:
  

  • http://stackoverflow.com/questions/4481388/why-does-mysql-higher-limit-offset-slow-the-query-down
  • http://explainextended.com/2009/10/23/mysql-order-by-limit-performance-late-row-lookups/

  Configuration
  Under "Performance":
  View attachment 2209
• Discussion

  This resource has a discussion thread on the forum where you can ask questions, report issues, and talk with the author and other users.

  Join the discussion (2)