0

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?

0

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.

  • Cheers! Why always use aliases? – mesqueeb Mar 17 '16 at 14:14
  • Just because that allow to avoid ambiguity before it occur. I'm used to set single-letter alias for every table used in the query. Also, my IDE for mysql provide code-completion and work pretty well with aliases. So when I've enter w. IDE shows popup with possible variants. – Kondybas Mar 18 '16 at 14:33

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.