Skip to content

Instantly share code, notes, and snippets.

@sabrina-zeidan

sabrina-zeidan/compare_cat_queries.php

Created July 29, 2024 15:55
Show Gist options
  • Save sabrina-zeidan/6cc06bfb4867697fa5bd46d957976e95 to your computer and use it in GitHub Desktop.
Save sabrina-zeidan/6cc06bfb4867697fa5bd46d957976e95 to your computer and use it in GitHub Desktop.
Download ZIP
Compare performance of query that retrieves posts by category [WordPress]
Raw
compare_cat_queries.php
//SZ Compare queries performance
// Function to fetch and display random posts using the initial slow method
function fetch_random_posts_initial() {
$start_time = microtime(true);
$args = array(
'cat' => array(1, 9, 10, 11, 44, 94),
'posts_per_page' => 3,
'no_found_rows' => true,
'ignore_sticky_posts' => true,
'orderby' => 'rand',
);
$news_query = new WP_Query($args);
if ($news_query->have_posts()) { ?>
<div class="teaser_wrap">
<div class="teaser_news">
<div class="news__inner">
<?php while ($news_query->have_posts()) : $news_query->the_post();
$thumb = wp_get_attachment_image_src(get_post_thumbnail_id(get_the_ID()), 'full');
if ($thumb) { ?>
<div class="news__item" style="background-image: url('<?php echo esc_url($thumb[0]); ?>')">
<a href="<?php the_permalink(); ?>" class="news__link">
<span class="news__title">
<?php the_title(); ?>
</span>
</a>
</div>
<?php } ?>
<?php endwhile; ?>
</div>
</div>
</div>
<?php }
wp_reset_postdata(); // Restore global post data stomped by the_post().
$end_time = microtime(true);
$execution_time = $end_time - $start_time;
echo '<!-- Initial method execution time: ' . $execution_time . ' seconds. -->';
}
// Function to fetch and display random posts using the optimized WP_Query method
function fetch_random_posts_optimized() {
$start_time = microtime(true);
// Fetch random post IDs
$categories = array(1, 9, 10, 11, 44, 94);
$args = array(
'cat' => $categories,
'posts_per_page' => 3,
'orderby' => 'rand',
'fields' => 'ids',
'no_found_rows' => true,
'ignore_sticky_posts' => true,
);
$random_posts = new WP_Query($args);
$random_post_ids = $random_posts->posts;
// Fetch posts using the random IDs
if ($random_post_ids) {
$args = array(
'post__in' => $random_post_ids,
'posts_per_page' => 3,
'orderby' => 'post__in',
'no_found_rows' => true,
'ignore_sticky_posts' => true,
);
$news_query = new WP_Query($args);
if ($news_query->have_posts()) { ?>
<div class="teaser_wrap">
<div class="teaser_news">
<div class="news__inner">
<?php while ($news_query->have_posts()) : $news_query->the_post();
$thumb = wp_get_attachment_image_src(get_post_thumbnail_id(get_the_ID()), 'full');
if ($thumb) { ?>
<div class="news__item" style="background-image: url('<?php echo esc_url($thumb[0]); ?>')">
<a href="<?php the_permalink(); ?>" class="news__link">
<span class="news__title">
<?php the_title(); ?>
</span>
</a>
</div>
<?php } ?>
<?php endwhile; ?>
</div>
</div>
</div>
<?php }
wp_reset_postdata(); // Restore global post data stomped by the_post().
}
$end_time = microtime(true);
$execution_time = $end_time - $start_time;
echo '<!-- Optimized WP_Query method execution time: ' . $execution_time . ' seconds. -->';
}
// Function to fetch and display random posts using a direct SQL query
function fetch_random_posts_sql() {
global $wpdb;
$start_time = microtime(true);
$category_ids = implode(',', array(1, 9, 10, 11, 44, 94));
$random_posts_query = "
SELECT wp_posts.ID
FROM wp_posts
LEFT JOIN wp_term_relationships ON (wp_posts.ID = wp_term_relationships.object_id)
LEFT JOIN wp_term_taxonomy ON (wp_term_relationships.term_taxonomy_id = wp_term_taxonomy.term_taxonomy_id)
WHERE wp_term_taxonomy.taxonomy = 'category'
AND wp_term_taxonomy.term_id IN ($category_ids)
AND wp_posts.post_type = 'post'
AND wp_posts.post_status = 'publish'
GROUP BY wp_posts.ID
ORDER BY RAND()
LIMIT 3
";
$random_post_ids = $wpdb->get_col($random_posts_query);
// Fetch posts using the random IDs
if ($random_post_ids) {
$args = array(
'post__in' => $random_post_ids,
'posts_per_page' => 3,
'orderby' => 'post__in',
'no_found_rows' => true,
'ignore_sticky_posts' => true,
);
$news_query = new WP_Query($args);
if ($news_query->have_posts()) { ?>
<div class="teaser_wrap">
<div class="teaser_news">
<div class="news__inner">
<?php while ($news_query->have_posts()) : $news_query->the_post();
$thumb = wp_get_attachment_image_src(get_post_thumbnail_id(get_the_ID()), 'full');
if ($thumb) { ?>
<div class="news__item" style="background-image: url('<?php echo esc_url($thumb[0]); ?>')">
<a href="<?php the_permalink(); ?>" class="news__link">
<span class="news__title">
<?php the_title(); ?>
</span>
</a>
</div>
<?php } ?>
<?php endwhile; ?>
</div>
</div>
</div>
<?php }
wp_reset_postdata(); // Restore global post data stomped by the_post().
}
$end_time = microtime(true);
$execution_time = $end_time - $start_time;
echo '<!-- SQL query method execution time: ' . $execution_time . ' seconds. -->';
}
// Main function to call all methods and measure their performance
function compare_random_post_methods() {
fetch_random_posts_initial();
fetch_random_posts_optimized();
fetch_random_posts_sql();
}
// Hook the main function to the 'wp_loaded' action to run once on page load
//add_action('wp_loaded', 'compare_random_post_methods');
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment