Show and paginate only child categories with wordpress query

Question

I'm using this piece of code to show and paginate only child categories but this is horribly slow (49 queries in 9,799 seconds.).

Where did I make mistakes and how can I make this run faster?

<?php
$current_cat = get_query_var('cat');
$attributes = array('terms_per_page' => 20, 'taxonomy' => 'category');
// Sanitize and validate our inputs and set variables
$tpp = filter_var( $attributes['terms_per_page'], FILTER_VALIDATE_INT );
$taxonomy = filter_var( $attributes['taxonomy'], FILTER_SANITIZE_STRING );
// Make sure our taxonomy exists to avoid unnecessary work
if ( !taxonomy_exists( $taxonomy ) )
return false;
// Our taxonomy exists, lets continue
// Get the term count to calculate pagination.
$term_count = count( get_terms('category',array('hide_empty'=>'1','child_of' => $current_cat) ) );
// We have terms, now calculate pagination
$max_num_pages = ceil( $term_count / $tpp );
// Get current page number. Take static front pages into account as well
if ( get_query_var('paged')){
$paged = get_query_var('paged');
} elseif ( get_query_var('page')) {
$paged = get_query_var( 'page' );
} else {
$paged = 1;
}
// Calculate term offset
$offset = ( ( $paged - 1 ) * $tpp );
// We can now get our terms and paginate it
$args = array(
'number' => $tpp, // Amount of terms to return
'offset' => $offset, // The amount to offset the list by for pagination
'child_of' => $current_cat,
'orderby' => 'name', 
'order' => 'ASC', 
'hide_empty' => '1',
);
// Set our variable to hold our string
$output = '';
$wpbtags = get_terms( $taxonomy, $args );
if ( ! empty($wpbtags)) {
foreach ($wpbtags as $category) { 
//first get the current category ID
$cat_prefix = "category_";
$cat_id = $category->term_id;
//then i get the data from the database
$join_cat = $cat_prefix . $cat_id;
$cat_data = get_option($join_cat);?>
<a href="<?php echo get_category_link($category->term_id); ?>" onclick="ga('send', 'event', 'ClickNav', 'cats', 'Cat - <?php echo $category- >cat_name; ?>');">
<div class="one_fourth<?php echo ( ++$counter == 4 )?' last_column':''; ?> home-colour" title="<?php echo $category->name; ?>">
<?php if (!empty($cat_data['cat_img_url'])) : ?>
<h2 style="text-align:center"> <?php echo $category->name; ?> </h2>
<img class="res_img" src="<?php echo $cat_data['cat_img_url'] ;?>" alt="<?php echo $category->cat_name; ?>" width="310" height="209" />
<?php elseif (empty($cat_data['cat_img_url'])) :?>
<h2 style="text-align:center"> <?php echo $category->name; ?> </h2>
<img class="res_img" src="<?php echo get_stylesheet_directory_uri(); ?>/images/subcat-img-na.png" alt="Posts - <?php echo $category->cat_name; ?>"/>
<?php else : ?>
<?php endif; ?>
</div><!-- eof cat div --> </a>
<?php } ?> 
<?php /* COUNTS FOUR ADDS LAST COLUMN */ if ( $counter == 4 ) : ?>
<div class='clear'> </div>
<?php $counter = 0; endif; wp_reset_query(); ?>
<div class='clear'> </div>
<?php echo '<div class="wp-pagenavi" style="text-align:center">';
$big = 999999999; // need an unlikely integer
echo paginate_links( array(
'base' => str_replace( $big, '%#%', esc_url( get_pagenum_link( $big ) ) ),
'format' => '?paged=%#%',
'current' => max( 1, get_query_var('paged') ),
'total' => $max_num_pages = (ceil( $term_count / $tpp))
) );
echo '</div>';
?> 
<?php wp_reset_postdata(); ?>
<?php /* IF THERE'S NOTHING TO SHOW */ } else {?>
// Show something if there is no posts here
<?php } ?>

• t3chb0t: Does horribly slow mean it needs days, hours or minutes? How slow is slow and how fast should it be?
• bpy: @t3chb0t Sorry, I should provide a reference to "horribly slow" (49 queries in 9,799 seconds.). I already edited the question. Anything faster is a great improvement. Thanks!
• t3chb0t: That's great. Now it deserves some votes ;-)

Answer 1

The real problem here is that you are querying in loops (almost always a bad pattern) because you are relying on built-in Wordpress querying functions, rather than writing raw SQL to query all this data at once.

My guess is you can do a single query to get both the paginated results and the total number of rows without limit being applied (by using SQL_CALC_FOUND_ROWS).

You might consider opening StackOverflow question specifically about the multiple/slow query problems along with information on your database tables relevant to these queries to get a suggestion on how to write an appropriate query.

---

Outside of that, I find your code incredibly difficult to read, and while some of that might have to do with following style conventions in Wordpress (which really bad IMO), you should at least indent you code and place comments properly (i.e. not prepending comment on code lines).