Optimized List Queries by Xon

Optimized List Queries by Xon 2.12.0

No permission to download
Overview FAQ Updates (24) Reviews (4) History Discussion
Xon said:
MySQL/MariaDB are largely the same performance wise.
Click to expand...

Xon said:
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...
Click to expand...

Thanks @egerci and of course, @Xon! Does MariaDB have the same issue regarding large offsets as MYSQL?
 
@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

1545403033435.webp
 
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
 
Xon said:
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
Click to expand...
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 said:
I'm doing 100,100,50 but have no idea if that is optimal
Click to expand...
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.
 
"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.
 
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 :rolleyes:

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)
 
You must log in or register to reply here.
Back
Top Bottom