Optimized List Queries by Xon

Optimized List Queries by Xon 2.12.0

No permission to download
Compatible XF 2.x versions
  1. 2.1
  2. 2.2
  3. 2.3
Additional requirements
php 7.2+
Standard Library by Xon v1.20.0+
License
MIT Licence
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:
Configuration
Under "Performance":
options.webp


Contributing features or bug fixes
Please create a Github Pull request via the "More Information" link.

Contributions

If you appreciate this add-on, please consider a contribution via PayPal. Details will be provide via private conversation.

Please contact me if you wish for different licencing arrangements.
Related resources
Author
Xon
Downloads
362
Views
10,258
First release
Last update

Ratings

5.00 star(s) 4 ratings

More resources from Xon

Latest updates

  1. 2.12.0 - XenForo 2.3 support

    This addon is now available on https://atelieraphelion.com/ Require StandardLib v1.20.0+...
  2. 2.11.0 - Feature update

    Add support for @Bob's Review Management System
  3. 2.10.0 - Feature update

    Support conversation lists with large number of conversations (Requires SV/StandardLib v1.10+...

Latest reviews

I've used this for over a year on a large forum with over 20 million posts. It's been working with no problems. Honestly my benchmarking is not good enough to see if this really improved things, but it didn't break anything either.
Must have for large forums. New version has been a huge improvement for large Media Galleries. I have a lot of attachments and recently synced ALL my attachments with media gallery 2.2. I'm still doing some syncing but right now have 21,471 pages.

Here's the different in loading that page with and without the addon:

With: 1.2s
Without: 9.6s (!)
A great add-on that solves forums issues not only with query performance. But besides this, the ugliness of the developers that causes the XenForo Media Gallery rules. Thanks to this add-on, mysql can completely solve the problem with slow queries, in addition, it is ruled by such a disgrace https://xenforo.com/community/threads/xf-finder-join-index-hints.173114/. If you have an average forum, then you should think about this addition already.
Big forums suffer a lot of performance issues. It's great to see a top notch addon author like Xon try to lessen the load and make things faster. Thanks!
Back
Top Bottom