Optimized List Queries by Xon 2.11.0

[Andrus]

Pardon my ignorance... I am running MariaDB rather than MySQL. Will I have any issues using this addon?

 

[egerci]

Pardon my ignorance... I am running MariaDB rather than MySQL. Will I have any issues using this addon?

I am using MariaDB without any issue.

 

[Xon]

MySQL/MariaDB are largely the same performance wise.

 

[Andrus]

MySQL/MariaDB are largely the same performance wise.

Forum/conversation lists:

MySQL 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 will fix this. Maybe.

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

Thanks @egerci and of course, @Xon! Does MariaDB have the same issue regarding large offsets as MYSQL?

 

[Xon]

Yes, both MySQL and MariaDB have this behaviour.

 

[Xon]

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

2.2.3 - Bugfix update

• Fix 'show older items' in news feed would not return as expected

Read the rest of this update entry...

 

[Xon]

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

2.2.4 - Compatibility fix

• Compatibility fix for;
  • Multi Prefix add-on
  • @Banxix's [Banxix] Bump Thread add-on

Read the rest of this update entry...

 

[maxicep]

@Xon I don't understand these options exactly what should be depend of my board ?
So, can you please explain me that what is difference with -1, 50,100,200,500 or like ?

Thanks

 

[Xon]

Those queries only matter for large forums, as they change how XF queries for data to prevent slow-downs.

With stock XenForo, touching page 1000 of a forum is much slower than page 1

 

[maxicep]

Those queries only matter for large forums, as they change how XF queries for data to prevent slow-downs.

With stock XenForo, touching page 1000 of a forum is much slower than page 1

Yes we have about 300 nodes with subforums also about 7.5m post and 600K threads.

So, minumum value is better for these options ? or rather than may slow down the website ? What is the ideal settings for this large forum u though?
For example, should we say that 50 is better instead of 150 ?

 

[dethfire]

I'm doing 100,100,50 but have no idea if that is optimal

 

[maxicep]

I'm doing 100,100,50 but have no idea if that is optimal

Yes, thats why i'm asking to @Xon

I though 50 is should better than 100 setting related the query LIMIT & OFFSET performance issues.
But, does it effect to another issue like cpu/database performance totally? (other factors) i don't know.

 

[Xon]

"It depends"

Ultimately you need to test to see what setting works best, the 50/100 was me basically hitting that many pages back and seeing if the page was noticably slower before/after the threshold.

 

[maxicep]

Actually I'm thinking that the best choice for that value is thread count per page value in forum_view. It is 20 for me

I've tried that 20-50-150 and page generation (load) times is almost same.
No changes for me.

(600K threads, 7m posts, 350 nodes)

 

[Xon]

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

2.3.0 - Feature update

• Require XF2.1+
• Fix "Better xxx List Page Threshold" options actually accepting 0 as a valid value.
  • This is useful to work-around poor MySQL 8+/MariaDb 10.4 performance issues with Collaborative Threads add-on

Read the rest of this update entry...

 

[dethfire]

My homepage is displaying the sql array at the top. Disabled for now.

 

[Xon]

Whoops, some bits from a debug build was included!

 

[Xon]

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

2.3.0

• Re-release v2.3.0 without debug code

Read the rest of this update entry...

 

[Xon]

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

2.3.1 - Maintainance update

• Patch for XF2.1.8 and earlier sorting by position/post_date when getting latest replies, this causes an unexpectedly poor query performance
• Fix shim for News Feed index choice

Read the rest of this update entry...

 

[Xon]

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

2.4.0 - Feature update

• Shim reactions received lists (and reactions given if User Essentials is installed)
• Add various addon options into their own grouping

Read the rest of this update entry...