Which columns to index with LEFT JOIN and GROUP_CONCAT

Question

So i have a complicated MySQL search query, and I wonder which columns I need to index to speed up searching:

        SELECT wpp.ID, post_title, wp_terms.name AS category, wp_terms.slug AS slug, supplier_company, 
        GROUP_CONCAT(wp_terms.slug SEPARATOR ', ') AS allslug,
        GROUP_CONCAT(wp_terms.name SEPARATOR ', ') AS allcatname
        FROM wp_posts AS wpp
        LEFT JOIN wp_term_relationships ON wpp.ID = object_id
        LEFT JOIN wp_terms ON term_taxonomy_id = wp_terms.term_id
        LEFT JOIN wp_teleapo_supplier AS s ON wpp.post_author = s.ID

        WHERE post_type = 'post' 
        GROUP BY wpp.ID

1) How to speed up the group_concat?
2) Should I index the column for ON at my LEFT JOINs? Like: wpp.ID and object_id?

Answer 1

You have to create indices for all the fields you have used to bind tables or to restrict results:

wpp.ID 
object_id 
term_taxonomy_id 
wp_terms.term_id
wpp.post_author 
s.ID
post_type

Some of fields are already indexed as primary keys so do not duplicate indices, that can impact RAM consumption as well as performance.

First you have to do now - is to launch your query with EXPLAIN prefix and show the result.

N.B.

Always use aliases for joined tables, like third one in your code. Always specify explicitly table you refer to, like wp_terms.term_id or s.id.