Improving WordPress performances with lot of comments (on a single post) (1 post)

  1. Stefano Lissa
    Posted 4 years ago #


    I know it's unusual by a site I maintain has 33,000 comments on a single post. The site is configured to show comments in reverse order, paged and threaded.

    To render a single page of comment, WordPress loads all them, but 33,000 comments with their text in memory on a 64 bit environment consumes something like 200 megabyte of ram.

    So I wondered if it's possible to load only the id and parent id of each comment to rebuild threads inside the walker and then load the single full comment while rendering the final page.

    Hacking the core with that solution the memory usage changes from 200 to about 30 megabyte.

    The changes on the fly I made was on comment-template.php file:

    if ( $user_ID) {
      $comments = $wpdb->get_results($wpdb->prepare("SELECT comment_ID, comment_parent FROM $wpdb->comments WHERE comment_post_ID = %d AND (comment_approved = '1' OR ( user_id = %d AND comment_approved = '0' ) )  ORDER BY comment_date_gmt", $post->ID, $user_ID));
    } else if ( empty($comment_author) ) {
      $comments = $wpdb->get_results($wpdb->prepare("SELECT comment_ID, comment_parent FROM $wpdb->comments WHERE comment_post_ID = %d AND (comment_approved = '1' )  ORDER BY comment_date_gmt", $post->ID, $user_ID));
    } else {
    $comments = $wpdb->get_results($wpdb->prepare("SELECT comment_ID, comment_parent FROM $wpdb->comments WHERE comment_post_ID = %d AND ( comment_approved = '1' OR ( comment_author = %s AND comment_author_email = %s AND comment_approved = '0' ) ) ORDER BY comment_date_gmt", $post->ID, wp_specialchars_decode($comment_author,ENT_QUOTES), $comment_author_email));

    It's easy to identify the original lines on comment-template.php and the modifications are on select(s) to extract only the comment id ad the parent comment id and the use of a direct select instead of the comment API on the middle case.

    Then, on Walker_Comment start_el function I added (on top)

    global $wpdb;
    $comment = $wpdb->get_row($wpdb->prepare("SELECT * FROM $wpdb->comments WHERE comment_ID = %d LIMIT 1", $comment->comment_ID));
    wp_cache_add($_comment->comment_ID, $_comment, 'comment');

    To load the comment and refresh the cache.

    I understand that this approach generates a lot of queries to load the comments and that hack should be extended identifying the comment id(s) to be displayed and load them all in once with an "in clause" query, putting them on the comment cache.

    More, this behavior should be activated only when the post has a important number of comments so the pro vs cons on memory and database usage reach the "break point".

    BTW, on a 32 bit server the 33,000 comment was not a problem, but on a 64 bit server they are since 64 bit libraries tens to be more memory hungry :-).

    Stefano Lissa.

Topic Closed

This topic has been closed to new replies.

About this Topic