Voice counter not optimized properly

[januzi_pl]

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)

[januzi_pl]

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

$voices = $wpdb->get_col( $wpdb->prepare( "select sum( number ) from ( SELECT ( DISTINCT post_author ) as number FROM {$wpdb->posts} WHERE ( post_parent = %d AND post_status = '%s' AND post_type = '%s' ) as s1 ) union  ( SELECT ( DISTINCT post_author ) as number FROM {$wpdb->posts} 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.

[7mps]

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

[johnjamesjacoby]

In 6439:

Engagements: Remove bbp_get_topic() checks from engagements.

See #3083.

[johnjamesjacoby]

For 2.6

[johnjamesjacoby]

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.

[johnjamesjacoby]

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.