Slow latest topics widget

[januzi_pl]

Hello

I've noticed that the page is generating a lot of cpu usage/load. One of the reasons for that is latest topics widget.

Here's mysql slow log line (one of hundreds of them):

Query_time: 1.885874  Lock_time: 0.000100 Rows_sent: 5  Rows_examined: 82490

SELECT   wp_posts.ID FROM wp_posts  INNER JOIN wp_postmeta ON ( wp_posts.ID = wp_postmeta.post_id ) WHERE 1=1  AND (
  wp_postmeta.meta_key = '_bbp_last_active_time'
) AND wp_posts.post_type = 'topic' AND ((wp_posts.post_status = 'publish' OR wp_posts.post_status = 'closed')) GROUP BY wp_posts.ID ORDER BY wp_postmeta.meta_value DESC LIMIT 0, 5;

If I may suggest, there should be cron job and separated table that would keep n topic ids.

I hope that I've picked up proper component (API - Widgets)? If not then please forgive me and move it to more appropriate category.

PS. Yes, I know that I could use widget cache, but what's the point if "latest topics" will be served with 10+ minutes delay?

[johnjamesjacoby]

Hi @januzi_pl, thanks for creating this ticket.

Are you using any caching plugins? bbPress should work really well with most object-cache plugins out there, and they usually do a great job of reducing a lot of this type of reprocessing problem.

[januzi_pl]

Hello

I'm using W3TC with db and object cache enabled. Unfortunately it refuses to cache this query. I think that's because the cached elements have got 2,5GB in total.

If I may ask for one more thing: other slow queries should be put as different tickets, right?

[johnjamesjacoby]

Define "slow."

Compared to WordPress, all bbPress queries will be slower.

This query should not be returning 2.5GB worth of topics.

[januzi_pl]

Slow: taking longer than 1.5s, examining more than 50k records (like search function using like %% on wp_posts table that's having 600k replies; imagine the result for table with 4M replies) and making mysqld taking up more than 100% of cpu time. Topics/replies counter in wp-admin main screen: # Query_time: 1.323124 Lock_time: 0.000054 Rows_sent: 3 Rows_examined: 289289.

2.5Gigs are in total (at least this is what "du -csh *" says about object dir).

I'm not saying that bbpress is bad or anything. It could do some things better.

[johnjamesjacoby]

There are some optimizations we can do here.

I'll be looking at our widgets and cleaning up their query arguments in 2.6.

[johnjamesjacoby]

In 6506:

Widgets: Audit all widget classes:

• Use escaping GetText functions
• Fix typos
• Use more efficient queries for forum/topic/reply meta-boxes

Needs testing. See #3070.

[johnjamesjacoby]

More improvements will require #1925.