Very very slow query. How can I improve it?

the following query is used in my WordPress blog, it gets the categories that a user has posted in. When he has a post in the category, the name of the category is shown.

It is a very very slow query because it is a big database and I have problems with the hosting company.

Read More

I have 3 categories, with id 3 called News, 4 called Articles and 5 called Others. My code is:

<?php
$author = get_query_var('author');
$categories = $wpdb->get_results("
    SELECT DISTINCT(terms.term_id) as ID, terms.name, terms.slug, tax.description
    FROM $wpdb->posts as posts
    LEFT JOIN $wpdb->term_relationships as relationships ON posts.ID = relationships.object_ID
    LEFT JOIN $wpdb->term_taxonomy as tax ON relationships.term_taxonomy_id = tax.term_taxonomy_id
    LEFT JOIN $wpdb->terms as terms ON tax.term_id = terms.term_id
    WHERE 1=1 AND (
        posts.post_status = 'publish' AND
        posts.post_author = '{$post->post_author}' AND
        tax.taxonomy = 'category' )
    ORDER BY terms.term_id ASC
");
?>

<ul>
    <?php foreach($categories as $category) : ?>
<?php if ( ($category->ID == '3')   || ($category->ID == '4')  || ($category->ID == '5')) { ?>
    <li>
        <a href="<?php echo get_category_link( $category->ID ); ?>/?author_name=<?php echo $curuser->user_login; ?>" title="<?php echo $category->name ?>">
            <?php echo $category->name; ?>
        </a>
    </li>
<?php } ?>
    <?php endforeach; ?>
</ul>

Thank you all!

Related posts

Leave a Reply

2 comments

  1. Judging by the looks of the wordpress database I have lying around, I’m guessing there is no index on the columns you are using in your WHERE clause for the wp_posts table.

    Try adding an index like this:

    ALTER TABLE wp_posts ADD INDEX (post_author,post_status).

    I bet you see a speed up from that.

    The best thing to do however is to run that query manually with an EXPLAIN in front of the SELECT, and analyze the output.

  2. Have you indexes on posts.ID, relationships.object_ID, relationships.term_taxonomy_id, tax.term_taxonomy_id, tax.term_id, terms.term_id, posts.post_status, posts.post_author and tax.taxonomy ?