查询非常慢。我怎样才能改进它?

时间:2021-03-17 04:19:04

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.

在我的Wordpress博客中使用以下查询,它获取用户发布的类别。当他在该类别中有帖子时,将显示该类别的名称。

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:

我有3个类别,id 3叫做News,4叫做Articles,5叫做Others。我的代码是:

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

谢谢你们!

2 个解决方案

#1


3  

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.

根据wordpress数据库的外观来判断,我猜测在wp_posts表的WHERE子句中使用的列上没有索引。

Try adding an index like this:

尝试添加这样的索引:

ALTER TABLE wp_posts ADD INDEX (post_author,post_status).

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.

但最好的办法是使用SELECT前面的EXPLAIN手动运行该查询,并分析输出。

#2


0  

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 ?

您是否对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和tax.taxonomy进行索引?

#1


3  

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.

根据wordpress数据库的外观来判断,我猜测在wp_posts表的WHERE子句中使用的列上没有索引。

Try adding an index like this:

尝试添加这样的索引:

ALTER TABLE wp_posts ADD INDEX (post_author,post_status).

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.

但最好的办法是使用SELECT前面的EXPLAIN手动运行该查询,并分析输出。

#2


0  

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 ?

您是否对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和tax.taxonomy进行索引?