Skip to:
Content

Opened 2 years ago

Closed 21 months ago

#3083 closed defect (fixed)

Voice counter not optimized properly

Reported by: januzi_pl Owned by: netweb
Milestone: 2.6 Priority: highest
Severity: critical Version: 2.5.12
Component: Component - Replies Keywords: has-patch
Cc:

Description

Hello

So, every time when somebody adds reply to the topic, the bbpress voice counter runs query:

SELECT COUNT( DISTINCT post_author ) FROM wp_posts 
WHERE ( post_parent = 121646 AND post_status = 'publish' AND post_type = 'reply' ) 
OR ( ID = 121646 AND post_type = 'topic' );

which leads to the:

# Query_time: 74.156613  Lock_time: 0.000067 Rows_sent: 1  Rows_examined: 4156148

(Yes, 4 million records).

How to fix it:
Split query into two different ones, or use union. Here's explain for single queries:

select DISTINCT post_author FROM wp_posts 
WHERE ( ID = 121646 AND post_type = 'topic' )

type=const, key=PRIMARY, rows=1

SELECT DISTINCT post_author FROM wp_posts 
WHERE ( post_parent = 121646 AND post_status = 'publish' AND post_type = 'reply' ) 

type=ref, key=post_parent, rows=5


As for union this would return proper amount of distinct authors without the need to filter whole wp_posts table:

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

(0.0007 - 0.0010 sec)

Attachments (1)

3083.patch (1.2 KB) - added by johnjamesjacoby 21 months ago.
For 2.6

Download all attachments as: .zip

Change History (6)

#1 @januzi_pl
2 years ago

wp-content/plugins/bbpress/includes/topics/functions.php
line 2610:
replace old query with the

$voices = $wpdb->get_col( $wpdb->prepare( "select count( distinct ( number )) from ( 
    ((SELECT (  post_author ) as number FROM {$wpdb->posts} as p2 WHERE post_parent = %d AND post_status = '%s' AND post_type = '%s' )  ) union ( SELECT (  post_author ) as number FROM {$wpdb->posts} as p1 WHERE ID = %d AND post_type = '%s' ) )as s2 ;", $topic_id, bbp_get_public_status_id(), bbp_get_reply_post_type(), $topic_id, bbp_get_topic_post_type() ) );

And voila, done.

Last edited 2 years ago by januzi_pl (previous) (diff)

#2 @7mps
2 years ago

Hello,

I need one Help. How to fix this error?

This is My current Website : http://indianengineers.com/ Developed using by MyBB.

Now I am moved to New Theme. ( under Construction URL is : ) http://indian.dubaishoppingmalls.com/

I want Move Current Website Datas ( user details, forums Articles and etc ) move to new theme BuddyPress. That has an option to import MYBB Database.

When I try it Shows Error like

Repair any missing information: Continue

No reply_to parents to convert

No replies to convert

No tags to convert

No super stickies to stick

No stickies to stick

No stickies to stick

#3 @johnjamesjacoby
21 months ago

In 6439:

Engagements: Remove bbp_get_topic() checks from engagements.

See #3083.

@johnjamesjacoby
21 months ago

For 2.6

#4 @johnjamesjacoby
21 months ago

  • Keywords has-patch added
  • Milestone changed from Awaiting Review to 2.6
  • Owner set to netweb

Assigning to @netweb to look into whether UNION queries are safe for:

  • Supported MySQL versions
  • Supported MySQLi versions
  • Supported MariaDB versions (probably same as MySQL)
  • HyperDB and other WPDB drop-ins (LudicrousDB, etc...)

I believe the attached patch is A-OK, but because nothing in core uses UNION style queries for anything, I'm reluctant to commit this right away.

#5 @johnjamesjacoby
21 months ago

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

In 6446:

Engagements: Performance optimizations to bbp_update_topic_voice_count().

  • Use a UNION query strategy (only on the wp_posts table)
  • Compare old count to new count before deleting & updating engagements

Fixes #3083. Props januzi_pl.

Note: See TracTickets for help on using tickets.