sql多对多查询索引优化

I have a many-to-many query that i'd like to optimize, what indexes should i create for it?

我有一个多对多查询，我想要优化它，我应该为它创建什么索引?

  SELECT (SELECT COUNT(post_id) 
            FROM posts 
           WHERE post_status = 1) as total,
         p.*,
         GROUP_CONCAT(t.tag_name) tagged
    FROM tags_relation tr  
    JOIN posts p ON p.post_id = tr.rel_post_id  
    JOIN tags t ON t.tag_id = tr.rel_tag_id
   WHERE p.post_status=1
GROUP BY p.post_id

EXPLAIN

解释

id  select_type table   type    possible_keys   key key_len ref rows    Extra
1   PRIMARY     p   ALL PRIMARY NULL    NULL    NULL        5   Using where; Using filesort

1 个解决方案

#1

You can take a look at the query execution plan using the Explain statement. This will show you whether a full table scan is happening or if it was able to find an index to retrieve the data. From that point on you can optimize further.

您可以使用Explain语句查看查询执行计划。这将显示是否正在进行全表扫描，或者是否能够找到检索数据的索引。从这一点上你可以进一步优化。

Edit

编辑

Based on your query execution plan, first optimization step check your tables have the primary key defined and you can set an index on post_status and tag_name columns.

根据您的查询执行计划，第一个优化步骤检查您的表有主键定义，您可以在post_status和tag_name列上设置索引。

#1