WordPress archive menu causing slow load times

1 Views

I have put together this archive menu, but it’s causing the page to load super slow.

I think the culprit:

Read More

PHP 

$years = $wpdb->get_col(
                "SELECT DISTINCT YEAR(post_date) 
                FROM $wpdb->posts 
                WHERE post_status = 'publish' 
                AND post_type = 'post' 
                ORDER BY post_date 
                DESC");

$months = $wpdb->get_col(
                      "SELECT DISTINCT MONTH(post_date) 
                      FROM $wpdb->posts 
                      WHERE post_status = 'publish' 
                      AND post_type = 'post' 
                      AND YEAR(post_date) = '".$year."' ORDER BY post_date DESC");

I’m using this data to echo a list of dates and post counts, is there something wrong with this method that causes it to be slow and is there a better method?

The aim of this function is to let me display the year and month there has been a post, so my own little archive sidebar.

With the aim of doing something like: 

foreach($years as $year){
 //..do something
    foreach($months as $month) {
    //... do something
    }
}

I have 1507 posts at the moment and it takes a good ~15s to load the archive page. Again, is there something wrong with the way I’m doing this?

Post Views: 1

Related posts

Leave a Reply

2 comments

  1. As an indirect answer, you might want to look at the wp_get_archives() function rather than rolling your own code.

    To answer your issue specifically, the problem here is that your second query is called once for every year returned by the first, and you’re probably then doing another query for each month to determine the number of posts. You should do one query which fetches all years, months and counts in one go.

    $dates = $wpdb->get_results( "
    SELECT YEAR( post_date ) AS year, MONTH( post_date ) AS month, count(*) as count
    FROM $wpdb->posts
    WHERE post_type = 'post'
    AND post_status = 'publish'
    GROUP BY year, month
    ORDER BY post_date DESC
" );

    You’ll then have an array of objects that contain the year, month and count properties which you can iterate over.

    foreach ( $dates as $date ) {
    // For example:
    echo '<p>' . $date->month . '/' . $date->year . ': ' . $date->count . '</p>';
}

  2. This method will always be slow and not scale. You have no limit on the query, so it either needs to get all or run until it times out. Worse yet running 2 queries with no limit.

    Either way its bad.

    You should be using WordPress’s built in archive functions or custom WP_Query with a limit and pagination.