No index is being used according to that plan, which means that given how much data you have, MySQL thinks a table scan is faster than using the index. It could have used one of those indexes, but it decided not to.
See what the explain looks like for this:
SELECT tags_data.company_id, GROUP_CONCAT(tags.name ORDER BY tags.name ASC SEPARATOR ‘,’) AS classifications FROM tags_data INNER JOIN tags USING(tag_id) GROUP BY tags_data.company_id
If that’s not terrible (the product of the rows columns is not huge), you can try using it to eliminate the last left join.
SELECT contacts.contact_name, companies.company_name, company_tags.classificationsFROM contactsLEFT JOIN companies USING(company_id)LEFT JOIN ( SELECT tags_data.company_id, GROUP_CONCAT(tags.name ORDER BY tags.name ASC SEPARATOR ‘,’) AS classifications FROM tags_data INNER JOIN tags USING(tag_id) GROUP BY tags_data.company_id ) AS company_tagsON companies.company_id = company_tags.company_idWHERE contacts.subscribed=‘VIP’
However, I’m not sure this is a good idea, since that inner select doesn’t get any benefit from the restriction applied to contacts and therefor companies. It will always load all your tags, which might be fine if that’s the common case.
I also didn’t try either of these myself, so you might have to clean up any syntactic problems I have.
The other thing you should consider since you’re implementing tags and you’re already using MyISAM, is using fulltext search. You’d end up just adding a text column to companies that contains a comma separated list of tags and creating a fulltext index on that column.
The query you’re trying to write will then stop after the left join into companies, and your classifications will already be available to you without any grouping. As an added bonus, searching by tags, especially a combination of tags, can be done through the fulltext index instead of some awful looking query which joins the tags table over and over.
You’ll need to change your code to maintain this column, which is probably best done outside of SQL in whatever language you’re already using for your application.