Ask your WordPress questions! Pay money and get answers fast! (more info)

Too many queries WordPress

  • SOLVED

I have some code on my site that is making 175 queries in 0.596 seconds on the front page. I am not sure how to optimize it to reduce the load. Maybe I need to enable transients on it? The code displays the number over the category thumbnail on the front page (scroll down to the thumbnail grid and hover over them). I am a developer, but more of a front-end guy. I would like to have someone implement the code for me, but also explain it so I can learn.

My site is: https://www.scholarships360.org/

The code is:

/**
* Function to list all category with thumbnail custom link, etc..
*
* How to use this function:
* Add in template: <?php my_category_list(); ?>
*
*/
function my_category_list(){

/* LIST OF CATS DATA */
$cats_data = array();

/**
* Get Categories
* @link http://codex.wordpress.org/Function_Reference/get_categories
*/
$cat_args = array(
'hide_empty' => 0,
'exclude' => '13,1,1460'
);
$categories = get_categories( $cat_args );

/* If category found, load list of category */
if ( !empty( $categories ) ) {
$i = 0;

/* Foreach category: display the data */
foreach ( $categories as $cat) {

/* ======= HTML CLASS ========= */
/* dynamic class (?) need fix */
$class = ( $i % 3 ) ? 'span4' : 'span4';
$classes = $class . ' ' . 'category-' . $cat->term_id;

/* ======= POST COUNT ========= */
/* Get all posts in category + in due date
* this only to get the post count.
* @link http://themehybrid.com/support/topic/issue-with-filtering-due-meta-key
*/
$query_args = array(
'post_type' => 'post',
'category_name' => $cat->slug,
'meta_query' => array(
array(
'key' => 'Due',
'value' => date( 'Ymd' ),
'type' => 'DATE',
'compare' => '>=', // greater than or equal to
)
)
);
$my_query = new WP_Query( $query_args );
$post_count = $my_query->found_posts;

/* ====== CATEGORY THUMBNAIL ======== */
$thumbnail_id = get_option('seamless_term_thumb_' . $cat->term_id);
$image = wp_get_attachment_url($thumbnail_id);

/* ====== SITE URL VARIABLE ======== */
$url = site_url();

/* ====== LINK TO SEARCH: no need fields ======= */
$link_to = $url . '/discover/?search_query=&orderby=due&tax_category=' . $cat->slug .'&wpas=1';

/* MERGE DATA IN ARRAY */
$cats_data[] = array(
'classes' => $classes,
'post_count' => $post_count,
'image' => $image,
'name' => $cat->name,
'link' => $link_to,
);

$i++;
} // end foreach

/**
* NOW THE FUN PART
* =================
*/

/* Sort Cat Data by Post Count */
usort($cats_data, 'my_sort_cat_data');

/* Cut only 6 item to display */
$cats_data = array_slice( $cats_data, 0, 6 );

/* Display it */
foreach ($cats_data as $cat_data ){ ?>

<div class="<?php echo $cat_data['classes'];?>">
<div class="thumb one">
<a href="<?php echo $cat_data['link'] ?>">
<div class="two"><?php echo $cat_data['post_count'] . ' Scholarships' ?></div>
</a>
<a href="<?php echo $cat_data['link'] ?>">
<img src="<?php echo $cat_data['image']; ?>" alt="<?php echo esc_attr( $cat_data['name'] ); ?>" class="item-image">
</a>
</div> <!-- end .thumb -->
</div>
<?php
}
}
/* No category found */
else {
echo '<p>No category found...</p>';
}
}

Answers (4)

2014-08-24

Dbranes answers:

Hi @Jagst3r21

If you want to use only a single database query to get the number of posts before the due date, then you can try the following approach:

/**
* Fetch taxonomy terms statistics for all posts before due date, with a single database query.
*
* @see http://www.wpquestions.com/question/showChronoLoggedIn/id/9770
* @param string $taxonomy
* @param string $meta_key
* @return mixed Database query results
*/

function get_tax_terms_stats_before_due_date( $taxonomy, $meta_key )
{
global $wpdb;

$sql ="
SELECT COUNT(1) as postscount, t.term_id, t.name, t.slug, tt.taxonomy
FROM {$wpdb->term_relationships} tr
INNER JOIN {$wpdb->posts} p ON p.ID = tr.object_id
INNER JOIN {$wpdb->postmeta} pm ON ( p.ID = pm.post_id AND meta_key = '%s' )
INNER JOIN {$wpdb->term_taxonomy} tt ON tr.term_taxonomy_id = tt.term_taxonomy_id
INNER JOIN {$wpdb->terms} t ON tt.term_id = t.term_id
WHERE
CAST( pm.meta_value AS DATETIME ) > '%s'
AND p.post_status = 'publish'
AND tt.taxonomy = '%s'
GROUP BY t.term_id
ORDER BY postscount DESC
";

return $wpdb->get_results( $wpdb->prepare( $sql, $meta_key, date( 'Y-m-d H:I:s' ), $taxonomy ) );
}


Here's an example how we can use this function:


/**
* Display the post count for each category before the due date.
*/

function wpq_list_stats_table_example()
{
$items = get_tax_terms_stats_before_due_date( 'category', 'due' );
$tr = '<tr><th>#</th><th>Term</th><th>Slug</th><th>Taxonomy</th></tr>';
foreach( $items as $item )
{
$tr .= sprintf( '<tr><td>%s</td><td>%s</td><td>%s</td><td>%s</td></tr>',
$item->postscount,
$item->name,
$item->slug,
$item->taxonomy
);
}
printf( '<table>%s</table>', $tr );
}

// Display it:
wpq_list_stats_table_example();



where it will display a table like this one:

# Term Slug Taxonomy
2 Tech tech category
2 Music music category
1 Academic academic category
1 Sport sport category


I hope you can modify this to your needs.

<strong>Update:</strong>

Here's a how you can use it in your code snippet:


/* Fetch data for categories + posts stats */
if( function_exists( 'get_tax_terms_stats_before_due_date' ) ) {
$categories = get_tax_terms_stats_before_due_date( 'category', 'due' );
}

/* Init */
$i = 0;
$cats_data = array();
$exclude_cats = array( 1, 13, 1460 );

/* Foreach category: display the data */
foreach ( (array) $categories as $cat) {

/* ======= EXCLUDE CATEGORIES ===== */
if( in_array( $cat->term_id, $exclude_cats ) ) continue;

/* ======= HTML CLASS ========= */
$class = ( $i % 3 ) ? 'span4' : 'span4';
$classes = $class . ' ' . 'category-' . $cat->term_id;

/* ====== CATEGORY THUMBNAIL ======== */
$thumbnail_id = get_option('seamless_term_thumb_' . $cat->term_id);
$image = wp_get_attachment_url($thumbnail_id);

/* ====== SITE URL VARIABLE ======== */
$url = site_url();

/* ====== LINK TO SEARCH: no need fields ======= */
$link_to = $url . '/discover/?search_query=&orderby=due&tax_category=' . $cat->slug .'&wpas=1';

/* ====== POST COUNT ========*/
$post_count = $cat->postscount;


/* MERGE DATA IN ARRAY */
$cats_data[] = array(
'classes' => $classes,
'post_count' => $post_count,
'image' => $image,
'name' => $cat->name,
'link' => $link_to,
);

$i++;
} // end foreach


foreach ($cats_data as $cat_data ){ ?>
<div class="<?php echo $cat_data['classes'];?>">
<div class="thumb one">
<a href="<?php echo $cat_data['link'] ?>">
<div class="two"><?php echo $cat_data['post_count'] . ' Scholarships' ?></div>
</a>
<a href="<?php echo $cat_data['link'] ?>">
<img src="<?php echo $cat_data['image']; ?>" alt="<?php echo esc_attr( $cat_data['name'] ); ?>" class="item-image">
</a>
</div> <!-- end .thumb -->
</div>
<?php }


ps: It's a good rule to use only <strong>lowercase</strong> meta keys, i.e. <em>due</em> instead of <em>Due</em>.


Jagst3r21 comments:

Hi, I forgot that I have this code after the function I posted above:

/**
* Sort Cat Data Helper Function
* @link http://stackoverflow.com/questions/2699086/sort-multidimensional-array-by-value-2
*/
function my_sort_cat_data( $a, $b ){
return $b['post_count'] - $a['post_count'];
}


Does this change things?


Dbranes comments:

You don't need to do any sorting in PHP when you use the <em>get_tax_terms_stats_before_due_date()</em> function, it's taken care of in SQL via:

ORDER BY postscount DESC


Jagst3r21 comments:

How do I display this in my template?


Dbranes comments:

Just wrap my code usage example into another my_category_list_wpq() function

function my_category_list_wpq(){
...
}


and add the <em>get_tax_terms_stats_before_due_date()</em>function into your <em>functions.php</em> or a plugin.


Dbranes comments:

@Hariprasad good catch, thank you ;-)

Updated the answer.


Jagst3r21 comments:

Dbranes' answer was perfect. We also optimized the code with transients so it is cached now too.

2014-08-24

timDesain Nanang answers:

1. populate all cats
2. create new array to store cat data and post count
3. count all posts by category
3. sort by post count
4. foreach new cat's array and break if 6.

try this :

function wpq_selected_cat(){
$args = array(
'hide_empty' => 1,
'exclude' => '13,1,1460',
'taxonomy' => 'category',
);
$all_cats = get_categories( $args );


$new_cats = array();
$post_cats = array();
foreach($all_cats as $key=>$term){
$term_id = $term->term_id;
$query_args = array(
'post_type' => 'post',
'category_name' => $term->slug,

'meta_query' => array(
array(
'key' => 'Due',
'value' => date( 'Ymd' ),
'type' => 'DATE',
'compare' => '>=', // greater than or equal to
)
)

);
$my_query = new WP_Query( $query_args );
$post_count = $my_query->found_posts;

$post_cats[$term_id] = $post_count;
$new_cats[$term_id] = array(
'name'=> $term->name,
'slug'=> $term->slug,
);
}

//arsort() - sort associative arrays in descending order, according to the value
arsort($post_cats);
$i = 0;
foreach($post_cats as $term_id=>$count){
$class = ( $i % 3 ) ? 'span4' : 'span4';
$classes = $class . ' ' . 'category-' . $term_id;

$thumb_id = get_option('seamless_term_thumb_' . $term_id);
$thumb_url = wp_get_attachment_url($thumbnail_id);
$link_to = '';home_url('/discover/?search_query=&orderby=due&tax_category=' . $new_cats[$term_id]['slug'].'&wpas=1');

?>
<div class="<?php echo $classes;?>">
<div class="thumb one">
<a href="<?php echo $link_to ?>">
<div class="two"><?php echo $count . ' Scholarships' ?></div>
</a>
<a href="<?php echo $link_to ?>">
<img src="<?php echo $thumb_url; ?>" alt="<?php echo esc_attr( $new_cats[$term_id]['name'] ); ?>" class="item-image">
</a>
</div> <!-- end .thumb -->
</div>
<?php
$i++;
if($i>5) break;
}
}

2014-08-24

Remy answers:

You can limit your WP_Query to only return the ids and not all the fields. The result of a WP_Query is already stored in cache, so a transient won't be useful here :

$query_args = array(
'post_type' => 'post',
'category_name' => $cat->slug,
'meta_query' => array(
array(
'key' => 'Due',
'value' => date( 'Ymd' ),
'type' => 'DATE',
'compare' => '>=', // greater than or equal to
)
),
'fields' => 'ids'
);

2014-08-24

Hariprasad Vijayan answers:

Hello,

@Dbranes, table name is not dynamic in get_tax_terms_stats_before_due_date()

SELECT COUNT(1) as postscount, t.term_id, t.name, t.slug, tt.taxonomy FROM tfl_term_relationships tr

It maynot work in another wordpress.