Optimized List Queries by Xon 1.4.3

[Xon]

Xon submitted a new resource:

Optimized List Queries by Xon - A collection of replacement queries for getting lists of stuff aimed at large forums.

MySQL implements 'early row lookup' which results in the large select statement pulling in more data than is required. Maybe one day MySQL will fix this. Maybe.

This causes an issue as this is 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...

Read more about this resource...

 

[Xon]

Cheers @digitalpoint - if you do ever consider making that addon then please do let me know . That market place is DAMN fast!

This addon should help when dealing with forums with very high page counts. It implements one of the suggestions (use a sub-select and only return a list of Ids, then get the full data).

It's more an issue with how MySQL needs to process all records before the limit clause (meaning it has to process all records before the ones you want thousands of pages deep).

I actually started working on a different way I getting threads (not using MySQL LIMIT). It's how I do it here: https://marketplace.digitalpoint.com/sites page 10000 would be just as fast as page 1... I started to make an addon to apply the same system to threads in forums, but I got busy doing other more important stuff and never finished it.

Was the idea to use something like a 'position' column like the posts table does?

I was considering that one, but wanted to get something out fast to address some slow-downs that were being experienced.

 

[nrep]

Thanks @Xon, I'm trialling it now and will let you know how it goes . So far, I'm seeing about a 10-15% improvement in loading large forum sections (around 8000 pages - 3.1s down to 2.7s)

 

[Xon]

Thanks @Xon, I'm trialling it now and will let you know how it goes . So far, I'm seeing about a 10-15% improvement in loading large forum sections (around 8000 pages - 3.1s down to 2.7s)

How far back is this? I was only going back ~3600 pages and seeing a rather large improvement but I likely could be done better.

 

[nrep]

That was right at the end of the 8000 pages, but I saw a similar improvement when I tested a handful of mid-range pages (4000ish).

 

[Xon]

Xon updated Optimized List Queries by Xon with a new update entry:

Potential Performance improvements.

This addon analyses the SQL generated by XenForo for thread listings, in an attempt to generate a better final query. Other addons may cause this approach to fail!

Please test this addon carefully.

This approach may fail to generate a valid query, in which case an exception is logged and the standard XenForo SQL will be executed.

Read the rest of this update entry...

 

[lazy llama]

Looks good, made a vast improvement on my test system, from 7 seconds to under a quarter of a second picking a page out of 5600 in one forum.
We also have a conversation with 174,000+ replies, so this should help with that too.

 

[Xon]

We also have a conversation with 174,000+ replies, so this should help with that too.

That is a lot of messages in a conversation!

 

[HWS]

That is a lot of messages in a conversation!

If 174.000 people take part in that conversation, only 1 message per participant is needed.

 

[lazy llama]

I think we'd need a different sort of addon for 174,000 participants in one conversation!
Think of all those notifications.... and that sidebar listing participants might go on a bit.

 

[Wesker]

This works in 1.3?

 

[Xon]

This works in 1.3?

It should, as there hasn't been any major structural changes to the code it modifies for quite some time.

If it fails, it should log the error and fall back to the standard XenForo code.

But as always I recommend you test.

 

[Xon]

Xon updated Optimized List Queries by Xon with a new update entry:

Optimize "Members Online" sidebar

This update will stop listing normal users in the members online

The 'members online' sidebar fetches all active users (guests/robots/members) and does post-processing in php. This post-processing scales linearly as the number of users online increases. Including guests/robots!

This add-on now only shows staff and followed users who are online, while retaining the totals for the above categories.

A potentially breaking change is the list of members online (who are not...

Read the rest of this update entry...

 

[Xon]

Xon updated Optimized List Queries by Xon with a new update entry:

Bugfix release

Include enough xf_user fields for standard styling for staff list.

Read the rest of this update entry...

 

[rdn]

Will this work for Online List created on BD Widget?

 

[Xon]

Will this work for Online List created on BD Widget?

It doesn't. But you should be able to configure the BD Widget to caching for 30 seconds to a minute or so.

I hooked at a very high level to avoid altering the other functionality which uses the same backend of 'members online' list.

 

[rdn]

But you should be able to configure the BD Widget to caching for 30 seconds to a minute or so.

Sadly some widgets like Online List don't have caching feature or option.

 

[rdn]

OK, I use the default XenForo Online list template now to take advantage of your addon

 

[Xon]

OK, I use the default XenForo Online list template now to take advantage of your addon

Just keep in mind the list of members which shows up as 'online' is massively reduced since it only shows staff & people being followed. The totals are still correct.

 

[rdn]

By the way, this addon really drop my home timing with debug mode enabled.
From 0.3xxx now 0.17xxx