1

Ok - so using a WP_Query to count votes by a user. But now there are 23,000 votes and its using up lots of memory.

Is there a way i can make this WP_Query more efficient?

$args = array(
    'post_type' => 'vote',
    'author'=>$user,
    'meta_key'=>'comp_question',
    'meta_value'=>$question_id,
    'meta_compare'=>'=',
    'orderby'=>'date',
    'order'=>'desc'
);

$the_query = new WP_Query( $args );

any help appreciated! dc any help appreciated!

  • 1
    I would look to store the results in a transient. Depending on how often you need to recalc the data it could be a solution? – Cristian Feb 13 '13 at 12:34
  • Yes, as WP_Queryalready returns count in the $post_countvariable, I just would cache the result and update it when a user post a new vote. – Simon Feb 14 '13 at 13:10
  • yeh that's true. Just wondering if its lighter using sql? Also the below suggestion suggests disabling the cache? – v3nt Feb 14 '13 at 15:18
2

you can try some of these extra parameters:

 'update_post_term_cache' => false,
 'update_post_meta_cache' => false, 
 'cache_results'=>false,
 'no_found_rows' => true, 
 'posts_per_page'=>1,
 'post_status'='publish',

ps: you can measure the speed of your query when you are testing it with microtime()

http://php.net/manual/en/function.microtime.php

Edit:

Here is a another way:

function count_votes($user_id=1, $question_id=1, $post_type='vote', $meta_key='comp_question'){
    global $wpdb;

    $sql = "SELECT count(*) FROM {$wpdb->posts} INNER JOIN {$wpdb->postmeta} ON ( {$wpdb->posts}.ID = {$wpdb->postmeta}.post_id) WHERE ({$wpdb->posts}.post_author = %d) AND ({$wpdb->posts}.post_type = %s) AND ({$wpdb->posts}.post_status = 'publish' ) AND ({$wpdb->postmeta}.meta_key = %s) AND ({$wpdb->postmeta}.meta_value = %d)"; 

    // uncomment if you need to group it
    //$sql .= "  GROUP BY {$wpdb->posts}.ID"; 

    $count = $wpdb->get_var($wpdb->prepare($sql,$user_id,$post_type,$meta_key,$question_id));

    return $count;
}

and you can use it like this:

$user_id=1;
$question_id=1; 
$post_type='vote'; 
$meta_key='comp_question';

echo "votes: " . count_votes($user_id, $question_id, $post_type, $meta_key);

Edit 2:

Here is a function that returns an array of votes ids:

function get_votes($user_id=1, $question_id=1, $post_type='vote', $meta_key='comp_question'){
    global $wpdb;

    $sql = "SELECT {$wpdb->posts}.ID FROM {$wpdb->posts} INNER JOIN {$wpdb->postmeta} ON ( {$wpdb->posts}.ID = {$wpdb->postmeta}.post_id) WHERE ({$wpdb->posts}.post_author = %d) AND ({$wpdb->posts}.post_type = %s) AND ({$wpdb->posts}.post_status = 'publish' ) AND ({$wpdb->postmeta}.meta_key = %s) AND ({$wpdb->postmeta}.meta_value = %d)"; 

    // uncomment if you need to group it
    //$sql .= "  GROUP BY {$wpdb->posts}.ID"; 

    $ids=array();
    $ids = $wpdb->get_col($wpdb->prepare($sql,$user_id,$post_type,$meta_key,$question_id));

    return $ids;
}

you can use it like this:

$ids=get_votes($user_id, $question_id, $post_type, $meta_key);
print_r($ids);
  • cheers - though these seem to break the query... these work 'cache_results'=>false, 'posts_per_page'=>1, and looking to see if they reduce load. – v3nt Feb 13 '13 at 18:09
  • how are you counting the votes? is your query returning 23k posts that you are counting with php? If so I would use custom sql with count(*) instead to get it. – birgire Feb 13 '13 at 18:16
  • i'm not returning 23k votes but that's how many it has to search through. I'm only looking for 1 which tells me there must be a better way to do it. – v3nt Feb 14 '13 at 10:13
  • I updated the answer above with custom sql. – birgire Feb 14 '13 at 11:01
  • ps: changed it into a custom function that returns the count. – birgire Feb 14 '13 at 11:22

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.