Making WordPress.org

Opened 20 months ago

Closed 20 months ago

Last modified 20 months ago

#6455 closed defect (bug) (fixed)

the /view/all-topics view is frequently failing

Reported by: sterndata's profile sterndata Owned by: dd32's profile dd32
Milestone: Priority: high
Component: Support Forums Keywords:
Cc:

Description

https://i.imgur.com/iTWJwuk.png

This has started happening. Sometimes a refresh shows topics, sometimes one has to wait for a while before it works.

This is critical view for doing forum support.

Change History (9)

#1 @sterndata
20 months ago

Why do I need this view? Sure, I could open X tabs and look at each forum, but this is a lot faster and gives a better overview of what's going on, and it's something I do as a forum moderator several times a day. It's useful to find uncaught spammers, etc.

#2 @Clorith
20 months ago

I'm actually leaning towards removing this view in its entirety.

It's a view that by design is really heavy, and un-cached, and does not actually provide the experience we want to strive for, in my opinion.

I believe we're putting way too much effort into over-moderation and almost aggressive overwatch at times. We have the tools for the community to report things that should be looked at, and if those tools are not good enough, we should instead focus our efforts on making them good enough.

I believe this would reduce friction between WordPress.org representatives (in this case, moderators or above), and other users of the platform.

That's not to say that human eyes won't be needed, but picking up a little something here and there, as opposed to "hawking for people to jump on" (harsh emphasis for effect), is also a much friendlier expectation of moderators, and will hopefully reduce the workload placed upon them (and maybe help with reducing burnout in the process?).

#3 @sterndata
20 months ago

This is not "hawking for people to jump on", but trying to efficiently get an overview of the forums, find spam that's missed by Akismet, and try to recognize (by looking at the names of the latest repliers) good candidates for the support team.

#4 @cristiano.zanca
20 months ago

Hi all, I can add my 2 cents to keep the "view=all" view

in our locale forum we use that to avoid missing topics with no answer together with having the situation of all the topics under control, how many are solved, what kind of issue is the most asked, monitor crossposting

#5 @fierevere
20 months ago

It shouldn't be removed without viable alternative, its useful for global and rosetta forums right after checking pending, spam and modlook views.

#6 @sterndata
20 months ago

If this is a technical thing, then what about changing it to just produce the 25 most recently posted topics. That might make the SQL query more efficient. Because, really, I'm just looking at the first page of those results.

With respect to topics with no replies as an alternative, I think that might have to be tweaked to ignore the "support topic/not support topic" choice.,

#7 @dd32
20 months ago

  • Owner set to dd32
  • Status changed from new to accepted

https://wordpress.org/support/view/all-topics/?view=all

The query behind the main loop on this view is timing out after 30s..

SELECT SQL_CALC_FOUND_ROWS wp_posts.ID
FROM wp_posts
WHERE 1=1
AND wp_posts.post_parent NOT IN (21261,21262,21272)
AND wp_posts.post_type = 'topic'
AND ((wp_posts.post_status = 'publish'
OR wp_posts.post_status = 'pending'
OR wp_posts.post_status = 'trash'
OR wp_posts.post_status = 'closed'
OR wp_posts.post_status = 'spam'
OR wp_posts.post_status = 'archived'
OR wp_posts.post_status = 'private'))
ORDER BY wp_posts.ID DESC
LIMIT 0, 30

If we exclude ?view=all the query generally completes a bit faster, but still not super-fast:

SELECT SQL_CALC_FOUND_ROWS wp_posts.ID
FROM wp_posts
WHERE 1=1
AND wp_posts.post_parent NOT IN (21261,21262,21272)
AND ((wp_posts.post_type = 'topic'
AND (wp_posts.post_status = 'publish'
OR wp_posts.post_status = 'closed'
OR wp_posts.post_status = 'private'
OR wp_posts.post_status = 'hidden')))
ORDER BY wp_posts.ID DESC
LIMIT 0, 30

The post_parent limitation is the plugin/themes/reviews forum id's.

Removing the pagination functionality (SQL_CALC_FOUND_ROWS) would probably help greatly here.

Looking at the EXPLAIN for the queries:

id	select_type	table		type	possible_keys			key			key_len	ref	rows	Extra
1	SIMPLE		wp_posts	ref	type_status_date,post_parent	type_status_date	22	const	8763037	Using index condition; Using where; Using filesort

id	select_type	table		type	possible_keys			key			key_len	ref	rows	Extra
1	SIMPLE		wp_posts	ref	type_status_date,post_parent	type_status_date	22	const	8588080	Using index condition; Using where; Using filesort

and by removing the full-row-calc functionality:

id	select_type	table		type	possible_keys			key	key_len	ref	rows	Extra
1	SIMPLE		wp_posts	index	type_status_date,post_parent	PRIMARY	8	NULL	47	Using where

I think we can just remove SQL_CALC_FOUND_ROWS from the query for the main support forums, and force it to think there's 50 pages of results, that'll take care of the performance issues and not remove any major functionality for the view for those who actually want to use it.

#8 @dd32
20 months ago

  • Resolution set to fixed
  • Status changed from accepted to closed

In 12031:

Support Forums: Improve performance for the all-topics view by limiting results to posts in the last month.

Fixes #6455.

#9 @dd32
20 months ago

Realised we already had code to limit the all-replies view to posts in the last month, limiting the all-topics to the last-month for WordPress.org/support (NOT for localised forums) provides the significant performance boost needed here - with the query being sub-1s.

The query explain shows it's still scanning a bunch of rows, but it's not anywhere near as bad anymore. The query effectively just includes an additional AND ( wp_posts.ID >= %d )

id	select_type	table		type	possible_keys				key	key_len	ref	rows	Extra
1	SIMPLE		wp_posts	range	PRIMARY,type_status_date,post_parent	PRIMARY	8	NULL	187840	Using where
Last edited 20 months ago by dd32 (previous) (diff)
Note: See TracTickets for help on using tickets.