MySQL – Which Columns to Index with LEFT JOIN and GROUP_CONCAT

indexMySQLoptimizationperformancequery-performance

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?

Best Answer

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.

Related Solutions

Mysql – Help optimizing MySQL slow query

I would like to get rid of "Using temporary; Using filesort"

One of the problems I see is that you're using different GROUP BY and ORDER BY clauses. From the manual on how MySQL uses temporary tables:

If there is an ORDER BY clause and a different GROUP BY clause, or if the ORDER BY or GROUP BY contains columns from tables other than the first table in the join queue, a temporary table is created.

As soon as you create a temporary table, it will need to be sorted according to your ORDER BY clause, indicated by 'using filesort'.

This execution plan at leasts uses the indexes to appropriately limit the number of rows found.

I would also look through the docs on ORDER BY optimization.

MySQL Indexes – How to Manage Too Many Indexes

Rather than try to guess, you should download Percona Tools.

You should use the tool pt-duplicate-key-checker. It will compare all the indexes for you and give your the ALTER TABLE commands to drop the ones you do not need and still maintain fully compliance with all your index search needs. Here is the code to do it:

ETL_INDX_SCRIPT=/tmp/remove_indexes.sql
MYSQL_USER=root
MYSQL_PASS=rootpassword
MYSQL_CONN="-u${MYSQL_USER} -p${MYSQL_PASS}"
DB=mydb
pt-duplicate-key-checker ${MYSQL_CONN} -d ${DB} | grep "^ALTER TABLE " >> ${ETL_INDX_SCRIPT}
mysql ${MYSQL_CONN} -AN 2>/dev/null < ${ETL_INDX_SCRIPT}

If you not sure, look at the content of the index removal SQL script first

ETL_INDX_SCRIPT=/tmp/remove_indexes.sql
MYSQL_USER=root
MYSQL_PASS=rootpassword
MYSQL_CONN="-u${MYSQL_USER} -p${MYSQL_PASS}"
DB=mydb
pt-duplicate-key-checker ${MYSQL_CONN} -d ${DB} | grep "^ALTER TABLE " >> ${ETL_INDX_SCRIPT}
vi -R ${ETL_INDX_SCRIPT}

If the resulting script has no ALTER TABLE commands, you don't need anything done.

If there are ALTER TABLE commands and if you are ready, run the script with this

mysql ${MYSQL_CONN} -AN 2>/dev/null < ${ETL_INDX_SCRIPT}

Best Answer

Related Solutions

Mysql – Help optimizing MySQL slow query

MySQL Indexes – How to Manage Too Many Indexes

GIVE IT A TRY !!!

Related Question