Is there any better/faster code than this ? It shows the categories that an author had ever posted in

Question

I have this query that shows the categories that a user had posted in. For example if I have 3 cats, name NEWS, MEDIA, PUBLICATIONS and the author had only posted in the first two, it will show

NEWS

MEDIA

Is there any better code than this or an improvement because my db is really huge and it gives me throttling? Thank you.

<?php
$author = get_query_var('author');
$categories = $wpdb->get_results("
SELECT DISTINCT(terms.term_id) as ID, terms.name
    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 posts.post_author = '{$post->post_author}' ");
?>

<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>

Answer 1

Your SQL isn't limiting the term matching to categories only. You're also getting back tags and any other taxonomy. So the data coming back may be more than expected.

Taxonomy is a key in the term_taxonomy table, so it should be fast to eliminate the ones you don't need.

SELECT DISTINCT(terms.term_id) as ID, terms.name
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 AND tax.taxonomy = "category")
LEFT JOIN $wpdb->terms as terms ON tax.term_id = terms.term_id
WHERE posts.post_author = {$post->post_author}

Might help. Your DISTINCT also probably causes usage of a temp table for sorting and such. You might try using a GROUP BY instead, like this:

SELECT terms.term_id AS ID, terms.name
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
AND tax.taxonomy =  "category" ) 
LEFT JOIN $wpdb->terms AS terms ON tax.term_id = terms.term_id
WHERE posts.post_author = {$post->post_author}
AND terms.term_id IS NOT NULL 
GROUP BY terms.term_id

Might give better results. Set 'em both up, run 'em through phpMyAdmin and have it do an EXPLAIN on them, see which is giving better results.

Also, post_author is an int. You don't need quotes around it.

Answer 2

I am no huge expert on SQL side, but this seems like resource-intensive operation however you approach it.

On other hand it doesn't seem like type of information that is very dynamic so my suggestion would be to simply cache results with Transients API for decent interval.

And if going more complex info practically only changes when author publishes a post (or post is deleted) so you can selectively hook into such events to recalculate and store info for authors only then.