Take the 2-minute tour ×
WordPress Development Stack Exchange is a question and answer site for WordPress developers and administrators. It's 100% free, no registration required.

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!

share|improve this question
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? –  daniel Crabbe Feb 14 '13 at 15:18

1 Answer 1

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);
share|improve this answer
    
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. –  daniel Crabbe 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. –  daniel Crabbe 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

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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