Forums pagination breaks at page 100

[davidnaviaweb]

​https://wordpress.org/support/view/all-topics/ shows 50750 pages, but only can reach 99 pages. At number 100 it shows 404 not found error. It seems to be the same issue as in #496, but the fix should not be the same since forums knowlegde should not be trimmed.

[obenland]

@otto42 correct me if I'm wrong, but I'm pretty sure that's intentional and won't change anytime soon.

[Otto42]

This is definitely intentional. Search engines trying to index that far back cause much load and break the system. Very few people would go that far back in the history, as page 100 of those topics is reaching back into the 3 weeks old range.

It may be worth investigating if the load problem has changed since we moved off bbPress 1 to bbPress 2. Also note that this is only partially blocked, sandboxed users can see those pages without restriction. This might be used to see what the impact is on the queries.

[SergeyBiryukov]

A similar issue was resolved for Plugin Directory in #496 by limiting the number of pages to 99.

[Clorith]

#3519 was marked as a duplicate.

[dd32]

The query time on all-topics is shockingly bad, the query time on page 100 isn't much worse, but given the excessive query time I don't feel comfortable recommending the limitation of 99 pages be removed.

It's so bad that I'd nearly suggest disabling the view entirely until it can be fixed.

Looking closer at the query, it's due to the usage of SQL_CALC_FOUND_ROWS. If that were removed on larger forums and it just assumed that there's 99 pages of results, the query time would be far-far-far lower (less than 100ms, where as currently it can be as high as 10s).
Even with that fixed, the query takes longer on every additional page, 99 seems like a sane upper limit given the query conditions.

[Clorith]

I'm perfectly fine with limiting pagination at this point, finding such old topics by reading page after page isn't an expected user interaction any way, they'd search for content more so than anything else.

[Otto42]

I don't see any downside to disabling found rows on those problem queries. If it helps performance, then I'm okay with various counts being inaccurate sometimes.

[dd32]

In 6888:

Support Forums: Performance: Optimize the all-topics query by setting no_found_rows and limit pagination display to 99 pages.

Fixes #3414.

[dd32]

Just noting that this went into Hooks rather than Performance_Optimizations to apply to all support forums, rather than just English.

The all-topics view should be far more performant now.