Reply To: Slow to Post

@daniellajosnl

Participant

For our site, with 121 forums, +150k topics and +1.1m replies, it could take up 30s before something is posted. I traced it down to this query:


        //$voices = $wpdb->get_col( $wpdb->prepare( "SELECT COUNT( DISTINCT post_author ) FROM {$wpdb->posts} WHERE ( post_parent = %d AND post_status = '%s' AND post_type = '%s' ) OR ( ID = %d AND post_type = '%s' );", $topic_id, bbp_get_public_status_id(), bbp_get_reply_post_type(), $topic_id, bbp_get_topic_post_type() ) );

includes/topics/functions.php, line 2610. It uses an OR clause, and MySQL (only in 5.1.73?) isn’t able to use an index anymore after a certain amount of records in the wp_posts table. I replaced the query with an UNION statement, and it executes much faster now, since it is using indexes again (as the EXPLAIN statements show me on a database with and without this amount of replies/topics):


        $unionQuery = "SELECT COUNT( DISTINCT post_author )
            FROM (
                SELECT post_author
                FROM {$wpdb->posts}
                WHERE post_parent = %d AND post_status = '%s' AND post_type = '%s'
              UNION
                SELECT post_author
                FROM {$wpdb->posts}
                WHERE ID = %d AND post_type = '%s'
              ) AS alias;";
    $voices = $wpdb->get_col( $wpdb->prepare( $unionQuery, $topic_id, bbp_get_public_status_id(), bbp_get_reply_post_type(), $topic_id, bbp_get_topic_post_type() ) );

bbPress.org