Skip to:
Content

#3154 closed enhancement (duplicate)

Inefficient voice counter in bbp_update_topic_voice_count

Reported by: januzi_pl Owned by:
Milestone: Priority: lowest
Severity: minor Version: 2.5.13
Component: General - Performance Keywords: needs-patch
Cc:

Description

Hello again.

So, this function is in the includes/topics/functions.php on line 2597 and the query it runs is on the line 2610. The result of this query lands in mysql slow query log:

# Query_time: 1.444707  Lock_time: 0.000042 Rows_sent: 1  Rows_examined: 626317 
SELECT COUNT( DISTINCT post_author ) FROM wp_posts WHERE ( post_parent = 629431 AND post_status
= 'publish' AND post_type = 'reply' ) OR ( ID = 629431 AND post_type = 'topic' );

Explain says that this query can't use any indices and has to go through the whole wp_posts table.

Here's fix for that:

select count( distinct post_author ) from ( SELECT post_author FROM wp_posts WHERE post_parent = 629431 AND post_status = 'publish' AND post_type = 'reply' union select post_author from wp_posts where ( ID = 629431 AND post_type = 'topic' ) ) as sub

Explains says:

id select_type table type possible_keys key key_len ref rows Extra
1 PRIMARY <derived2> ALL NULL NULL NULL NULL 2
2 DERIVED wp_posts ref type_status_date,post_parent,post_type,post_status... post_parent 8 1 Using where
3 UNION wp_posts const PRIMARY,type_status_date,post_type PRIMARY 8 1
NULL UNION RESULT <union2,3> ALL NULL NULL NULL NULL NULL

Change History (4)

#1 @netweb
18 months ago

  • Severity changed from major to normal

Thanks @januzi_pl, what version of bbPress are using to test this?

Are you using the latest development version that includes the patch you provided in #3083?

#2 @januzi_pl
18 months ago

Oh, silly me, I've already reported this. Owner of the site would like to get the plugin as stable version I'm afraid, but I might convince him to change his mind. The question is: is there also fix in the RC for the post counters in topics/forums, so there won't be things like "SELECT COUNT(ID) FROM wp_posts WHERE post_parent IN ( press pgdown 15 times ) AND post_status = 'publish' AND post_type = 'reply';"?

#3 @januzi_pl
18 months ago

  • Priority changed from normal to lowest
  • Severity changed from normal to minor

#4 @johnjamesjacoby
10 months ago

  • Milestone Awaiting Review deleted
  • Resolution set to duplicate
  • Status changed from new to closed

Closing this one as a duplicate of #3083.

Note: See TracTickets for help on using tickets.