sql many-to-many query indexes optimization

Question

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

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

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.