Need help with slow queries/enabling transients WordPress
I have meta key "Due" for posts....if a post has expired (past the current date or not equal to it) then it will not be displayed in the count. This is my code, which works perfectly. My only problem is that it creates over 100 queries. Is there any way to reduce this number? I am not sure how to optimize it....
/**
* 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);
/* ====== LINK TO SEARCH: no need fields ======= */
$link_to = 'http://www.scholarships360.org/discover/?search_query=&orderby=blank&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>';
}
}
/**
* 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'];
}
Answers (3)
John Cotton answers:
Presumably it does 100 queries because you have a lot of categories and you do a WP_Query each time in the loop?
One option would be to use the category__in param of WP_Query and thus query for all categories at once. However, that would give you an array of posts for which you don't know the category so you'd still have to do multiple queries in the posts loop to determine category, albeit only for the number of posts you have.
So, I think the only way you could avoid that is a custom SQL query that returns the post info AND the category info in one go.
Is the trade off of performance against custom code worth it?
Jagst3r21 comments:
Thanks for the response...I am honestly not sure...I had asked on stack exchange and a few people said to use transients to mitigate the toll on the database. Does that make sense?
John Cotton comments:
<blockquote>a few people said to use transients to mitigate the toll on the database.</blockquote>
...possibly.
If the output of your code is used a lot, then caching the HTML result for a period (as a transient) would reduce the hits on the database.
So instead of 100 queries every minute, you might decide that the data is being updated infrequently enough for 100 hits every hour, or even every day.
Storing the result of the queries (really, you should capture the HTML to a buffer and store that) for a period that matches your post update frequency is a sensible suggestion.
Dbranes answers:
Hi @Jagst3r21, this problem looks like this one:
[[LINK href="http://www.wpquestions.com/question/showChrono/id/9770"]]http://www.wpquestions.com/question/showChrono/id/9770[[/LINK]]
where we used a single custom SQL query + transients, if I recall correctly.
Jagst3r21 comments:
Hi...I vaguely remember that...but I don't think I ever implemented it for some reason...can you please post again how I integrate that into my functions.php and then display it in my page template?
Jagst3r21 comments:
Hi...tried this, but no luck:
/**
* 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 ) );
}
/**
* Display the post count for each category before the due date.
*/
function front_page_cat_list() {
/* 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 }
}
and am calling it in the template like:
front_page_cat_list
Dbranes comments:
It looks like you're missing this part:
$categories = get_tax_terms_stats_before_due_date( 'category', 'due' );
to fill upp the <em>$categories</em> within the <em>front_page_cat_list()</em> function.
Jagst3r21 comments:
I tried this, no luck:
/**
* 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 ) );
}
/**
* Display the post count for each category before the due date.
*/
function front_page_cat_list() {
$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 }
}
Dbranes comments:
What's the output of
$categories = get_tax_terms_stats_before_due_date( 'category', 'due' );
print_r( $categories );
Jagst3r21 comments:
I forgot to change category to scholarship_category haha. It works now...but how do I limit it to only show the first 6?
Dbranes comments:
You can try adding:
LIMIT 0, 6
at the end of the SQL query.
Jagst3r21 comments:
cool...now my homepage has 83 queries in 0.0173 seconds where before it was 175++ in 0.05+ seconds...big improvement right?
Dbranes comments:
great, glad to hear it worked ;-)
(than you can additionally use transients to store the data )
Jagst3r21 comments:
hmm...want to show me how to do that? :)
Dbranes comments:
You can try to replace:
$categories = get_tax_terms_stats_before_due_date( 'scholarship_category', 'due' );
with:
// Store data for 15 minutes:
if ( false === ( $categories = get_transient( 'wpq_data' ) ) ) {
$categories = get_tax_terms_stats_before_due_date( 'scholarship_category', 'due' );
set_transient( 'wpq_data', $categories, 15 * MINUTE_IN_SECONDS );
}
Jagst3r21 comments:
How to test if it works?
Dbranes comments:
You can do that like this:
$categories = get_transient( 'wpq_data' );
// Store data for 15 minutes:
if ( false === $categories ) {
$categories = get_tax_terms_stats_before_due_date( 'scholarship_category', 'due' );
set_transient( 'wpq_data', $categories, 15 * MINUTE_IN_SECONDS );
echo '<!--DEBUG: Generate data -->';
} else {
echo '<!--DEBUG: Fetching data from cache -->';
}
Jagst3r21 comments:
I put that inside:
if( function_exists( 'get_tax_terms_stats_before_due_date' ) ) {
........ insert that code here .........
}
like that right?
Also, I am trying to display the category description under the thumbnail.
I added
'description' => $cat->description,
to the cats_data array and called it like this:
<p><?php echo esc_attr($cat_data['description']); ?></p>
but no luck :(
Also, do you do larger scale jobs? I am in need of a programmer for another project and will pay...just need to know hourly rate...maybe u can pm me.
Dbranes comments:
You can get the term description by fetching the term object via <em>get_term()</em> or <em>get_term_by()</em>.
Another approach would be to try to add <em>tt.description</em> into the SQL query.
(sent you pm earlier today)
Just Me answers:
If you have a lot of empty subcategories I would consider using `hide_empty => 1` instead of `hide_empty => 0`
Did the alternative code you used not work at all or just not generate a better outcome. The command should be `front_page_cat_list();`, but maybe you used that already.
Jagst3r21 comments:
I tried:
<?php front_page_cat_list(); ?>
but it displays nothing...is there something wrong with the code?