• This site uses cookies. By continuing to use this site, you are agreeing to our use of cookies. Learn more.

Optimized List Queries by Xon 1.3.4

No permission to download

Xon

Well-known member
#1
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

Well-known member
#2
Cheers @digitalpoint - if you do ever consider making that addon then please do let me know :D. 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

Well-known member
#3
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

Well-known member
#4
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

Well-known member
#5
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

Well-known member
#6

lazy llama

Well-known member
#7
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.
 

lazy llama

Well-known member
#10
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.
 

Xon

Well-known member
#12
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

Well-known member
#13
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

Well-known member
#16
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.