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.
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!
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 thewp_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 theSELECT
, and analyze the output.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
andtax.taxonomy
?