Best Practice for speed, using $wpdb for every post displayed

  • Resolved pkchrisjohnson

    (@pkchrisjohnson)


    13 years, 8 months ago

    I am building a custom theme for a blog and community site with multiple authors. My goal is to display a visually styled badge indicating what “level” they are in the community next to an author’s name that is derived from the following equation…

    1. Comments are 1 point each
    2. Posts are 25 points each
    3. Being at a certain user level (editor or above) is 25 points per month
    4. Writing a post in a special category is 75 points

    I have determined how to do this using the $wpdb in a custom function and it is working properly. My question centers around whether this is a good idea to be making that many database calls each time a post author link is displayed. Is this going to significantly affect the performance of the site to the point where I would be better off doing something else? Here is an example of the function to get the number of comments written by the user_id stored in the $badge_user_id variable:
    $num_comments = $wpdb->get_var($wpdb->prepare("SELECT COUNT(*) FROM $wpdb->comments WHERE comment_approved = '1' AND user_id = $badge_user_id;"));

    What happens when the site has a few thousand comments. I assume that that query is going to take some time to compute and this significantly affect load time. Is there some way to fix this or make this more optimized?

Viewing 2 replies - 1 through 2 (of 2 total)
  • David Sader

    (@dsader)

    13 years, 8 months ago

    First suggestion, when using an expensive query – cache it.

    Second, do the comment counting only when a comment is submitted.

    Third, store the updated comment_count in usermeta with
    update_user_meta

    Finally, echo the comment_count from get_userdata

    That way the hit on the db is during the saving of the comment(and your cache refresh time), an infrequent task. The count can be echoed from the $current_user global on the one hand or from get_userdata for non logged in users on the other hand.

    Thread Starter pkchrisjohnson

    (@pkchrisjohnson)

    13 years, 8 months ago

    Thanks for the information. These suggestions should help a lot!

Viewing 2 replies - 1 through 2 (of 2 total)
  • The topic ‘Best Practice for speed, using $wpdb for every post displayed’ is closed to new replies.