# 3 way LEFT JOIN with GROUP CONCAT, eliminate using filesort **URL:** \ **Category:** Other MySQL® Questions\ **Created:** [November 23, 2008, 12:39pm UTC](https://forums.percona.com/t/3-way-left-join-with-group-concat-eliminate-using-filesort/1009 "2008-11-23T12:39:23Z")\ **Posts on this page:** 8\ **Page:** 1
**Author:** ![batfastad](https://avatars.discourse-cdn.com/v4/letter/b/df788c/32.png) [@batfastad](https://forums.percona.com/u/batfastad)\ **Post date:** [November 23, 2008, 12:39pm UTC](https://forums.percona.com/t/3-way-left-join-with-group-concat-eliminate-using-filesort/1009/1 "2008-11-23T12:39:23Z")
Hi everyone I’m relatively new to MySQL and gradually learning new stuff, but I’m having a problem with a particular query. I’ve asked around in the PHP DevNet forums and it seems I should be getting much better performance on this query, as my tables are pretty small and the query’s not particularly advanced. The structure is nicely normalised, something I’ve wanted to have for years. I’m gradually switching our company intranet subscribers database from filemaker to MySQL. My server is Ubuntu Server 8.04 LTS, MySQL5, PHP5 and Apache2. On a pretty recent Intel Core 2 server with 4GB memory. I’d really appreciate it if someone could give me some advice on this, as it’s been driving me crazy! contacts has about 36,000 records companies: 38,000 tags\_data: 42,000 tags: 980 My total DB size is about 26MiB, so it’s tiny in comparison to many MySQL databases. I think this query should not be taking 32 seconds to complete! Basically the tags table has various tags/classifications which can be applied to companies. So you can tag a company as MANUFACTURER, DISTRIBUTOR, SHOW EVENT ORGANISER… etc and there’s 980 different tags/classifications to choose from. Each company can have multiple tags applied, and the tags\_data table stores which tags are applied to which company. Here’s a quick diagram of part of the database which should help to visualise green bold are the primary key columns. Red are the columns which link the data together. Here’s the query I’m trying to run: SELECT `contacts`.`contact_name`, `companies`.`company_name`, GROUP\_CONCAT(`tags`.`name` ORDER BY `tags`.`name` ASC SEPARATOR ‘,’) AS `classifications`FROM `contacts`LEFT JOIN `companies` USING(`company_id`)LEFT JOIN `tags_data` ON `companies`.`company_id`=`tags_data`.`company_id`LEFT JOIN `tags` ON `tags_data`.`tag_id`=`tags`.`tag_id`WHERE `contacts`.`subscribed`='VIP’GROUP BY `contacts`.`contact_id` The syntax on that might be improved I guess, but I don’t know how to do that. That’s the limit of my MySQL knowledge really. I’m trying to get contact\_name, company\_name and a column called classifications which is a comma separated list of the tags/classifications names that are selected for that particular company. contacts.subscribed can have 3 different values: NULL, FREE or VIP This query only returns 777 records, but I’ll be looking to return up to 20,000 with a variation of this query if I can get it running faster! Initially this query took about 80 seconds, but I added a composite index (only just learnt about them) on the tags\_data table (index called tags\_companies) which reduced it to about 30 seconds. I removed the ORDER BY or LIMIT from the query earlier as well, but it made very little difference. Here’s the output of EXPLAIN on this query: id select\_type table type possible\_keys key key\_len ref rows Extra1 SIMPLE contacts ref IX\_subscribed IX\_subscribed 15 const 1103 Using where; Using temporary; Using filesort1 SIMPLE companies eq\_ref PRIMARY PRIMARY 4 dwcintranet.contacts.company\_id 1 1 SIMPLE tags\_data index company\_id tags\_companies 26 NULL 41930 Using index1 SIMPLE tags eq\_ref PRIMARY PRIMARY 4 dwcintranet.tags\_data.tag\_id 1 So I think the problem is the Using filesort. I’ve tried taking the query apart to eliminate the filesort, and it seems it’s the 2nd join that’s causing the filesort: tags\_data ON companies But I don’t know what else I could index to sort this out Any ideas/suggestions? I would really appreciate some help on this, I’m convinced that this query shouldn’t be taking 30 seconds! Thanks, Ben ---
**Author:** ![batfastad](https://avatars.discourse-cdn.com/v4/letter/b/df788c/32.png) [@batfastad](https://forums.percona.com/u/batfastad)\ **Post date:** [November 23, 2008, 12:44pm UTC](https://forums.percona.com/t/3-way-left-join-with-group-concat-eliminate-using-filesort/1009/2 "2008-11-23T12:44:58Z")
Here’s the SHOW CREATE TABLE of the 4 tables contacts CREATE TABLE `contacts` ( `contact_id` int(11) NOT NULL auto\_increment, `company_id` int(11) default NULL, `address1_contact` varchar(255) default NULL, `address2_contact` varchar(255) default NULL, `address3_contact` varchar(255) default NULL, `business_card` varchar(2) default NULL, `contact_id_old` int(20) default NULL, `contact_name` varchar(255) default NULL, `county_state_contact` varchar(100) default NULL, `create_name` varchar(60) default NULL, `create_stamp` int(10) default NULL, `email_contact` varchar(255) default NULL, `email_contact_optout` tinyint(1) default NULL, `fax_contact` varchar(255) default NULL, `geo_contact` varchar(255) default NULL, `job_title` varchar(255) default NULL, `subscribed` varchar(4) default NULL, `mail_contact` tinyint(1) default NULL, `modify_count` int(7) default ‘0’, `modify_name` varchar(60) default NULL, `modify_stamp` int(10) default NULL, `note` text, `postcode_zip_contact` varchar(50) default NULL, `reg_stamp` int(10) default NULL, `tel_cell` varchar(255) default NULL, `tel_contact` varchar(255) default NULL, `tel_ext` varchar(255) default NULL, `tel_home` varchar(255) default NULL, `temp` varchar(50) default NULL, `town_city_contact` varchar(100) default NULL, `view_count` int(7) default ‘0’, `view_name` varchar(60) default NULL, `view_stamp` int(10) default NULL, PRIMARY KEY (`contact_id`), KEY `company_id` (`company_id`), KEY `contact_name` (`contact_name`), KEY `IX_subscribed` (`subscribed`), KEY `companies_contacts` (`company_id`,`contact_id`), KEY `contacts_companies` (`contact_id`,`company_id`)) ENGINE=MyISAM AUTO\_INCREMENT=37645 DEFAULT CHARSET=utf8 companies CREATE TABLE `companies` ( `company_id` int(11) NOT NULL auto\_increment, `ad_lead` tinyint(1) default NULL, `address_priv` tinyint(1) default NULL, `address1` varchar(255) default NULL, `address2` varchar(255) default NULL, `address3` varchar(255) default NULL, `advisory` varchar(255) default NULL, `advisory_name` varchar(60) default NULL, `advisory_stamp` int(10) default NULL, `agency_address` varchar(255) default NULL, `agency_company` varchar(255) default NULL, `agency_contact` varchar(255) default NULL, `agency_email` varchar(255) default NULL, `agency_fax` varchar(255) default NULL, `agency_id` int(11) default NULL, `agency_tel` varchar(255) default NULL, `budget_year1` varchar(3) default NULL, `budget_year2` varchar(3) default NULL, `company_name` varchar(255) default NULL, `county_state` varchar(100) default NULL, `create_name` varchar(60) default NULL, `create_stamp` int(10) default NULL, `currency` varchar(3) default NULL, `dir_contact` varchar(255) default NULL, `email` text, `email_optout` tinyint(1) default NULL, `email_priv` tinyint(1) default NULL, `fax` varchar(255) default NULL, `fax_priv` tinyint(1) default NULL, `geo` varchar(2) default NULL, `hanging_file` tinyint(1) default NULL, `iso_code` varchar(20) default NULL, `locked_name` varchar(60) default NULL, `locked_stamp` int(10) default NULL, `master_id` int(11) default NULL, `modify_count` int(7) default ‘0’, `modify_name` varchar(60) default NULL, `modify_stamp` int(10) default NULL, `postcode_zip` varchar(50) default NULL, `rep` varchar(10) default NULL, `tel` varchar(255) default NULL, `tel_priv` tinyint(1) default NULL, `town_city` varchar(255) default NULL, `vat_number` varchar(20) default NULL, `view_count` int(7) default ‘0’, `view_name` varchar(60) default NULL, `view_stamp` int(10) default NULL, `website` text, PRIMARY KEY (`company_id`), KEY `geo` (`geo`), KEY `company_name` (`company_name`)) ENGINE=MyISAM AUTO\_INCREMENT=39200 DEFAULT CHARSET=utf8 tags\_data CREATE TABLE `tags_data` ( `tagdata_id` int(11) NOT NULL auto\_increment, `tag_id` int(11) default NULL, `company_id` varchar(6) default NULL, `create_name` varchar(60) default NULL, `create_stamp` int(10) default NULL, PRIMARY KEY (`tagdata_id`), KEY `tag_id` (`tag_id`), KEY `company_id` (`company_id`), KEY `tags_companies` (`tag_id`,`company_id`)) ENGINE=MyISAM AUTO\_INCREMENT=42205 DEFAULT CHARSET=utf8 tags CREATE TABLE `tags` ( `tag_id` int(7) NOT NULL default ‘0’, `category` varchar(255) default NULL, `description` varchar(255) default NULL, `name` varchar(255) default NULL, PRIMARY KEY (`tag_id`), KEY `category` (`category`), KEY `name` (`name`)) ENGINE=MyISAM DEFAULT CHARSET=utf8 I’d really appreciate it if someone could take a look through to see how I can speed this up! Thanks so much, Ben ---
**Author:** ![vgatto](https://sea1.discourse-cdn.com/flex019/user_avatar/forums.percona.com/vgatto/32/748_2.png) [@vgatto](https://forums.percona.com/u/vgatto)\ **Post date:** [November 24, 2008, 11:14am UTC](https://forums.percona.com/t/3-way-left-join-with-group-concat-eliminate-using-filesort/1009/3 "2008-11-24T11:14:42Z")
I think your problem is here: 1 SIMPLE tags\_data index company\_id tags\_companies 26 NULL 41930 Using index The previous line should produce a set of company ids used to join into tags\_data. Unfortunately, MySQL is doing an index scan for every row produced from the previous step. Basically, MySQL is evaluating 41930 \* 1103 rows to produce your result set. That’s over 40M, which explains why things are taking so long. I think you can clear this up by eliminating the left join from tags\_data into tags. Since this is a join table, you really shouldn’t have orphaned rows. If you do, they should be easy to clean up. You may also want another covering index on tags\_data, on (company\_id, tag\_id). This will be used in joins from companies to tags. Your existing index only helps when joining from tags to companies. Since you’re using MyISAM, and only indexes are cached, having (company\_id, tag\_id) instead of just company\_id should help things move faster as well. ---
**Author:** ![batfastad](https://avatars.discourse-cdn.com/v4/letter/b/df788c/32.png) [@batfastad](https://forums.percona.com/u/batfastad)\ **Post date:** [November 24, 2008, 11:23am UTC](https://forums.percona.com/t/3-way-left-join-with-group-concat-eliminate-using-filesort/1009/4 "2008-11-24T11:23:12Z")
Thanks for the reply. I figured that’s what was happening behind the scenes. I think I need the LEFT JOIN from tags\_data into tags, because I want to access the tags.name field in my GROUP CONCAT() Do I still need that particular LEFT JOIN to accomplish that? Cheers, B ---
**Author:** ![batfastad](https://avatars.discourse-cdn.com/v4/letter/b/df788c/32.png) [@batfastad](https://forums.percona.com/u/batfastad)\ **Post date:** [November 24, 2008, 3:03pm UTC](https://forums.percona.com/t/3-way-left-join-with-group-concat-eliminate-using-filesort/1009/5 "2008-11-24T15:03:29Z")
Thanks for the reply. I just changed that tags on tags\_data.tag\_id=tags.tag\_id into a regular JOIN, rather than LEFT JOIN And it ran much quicker, but it returned fewer results. I do want to return companies even if they have no tags selected in tags\_data. So I guess that has to stay a LEFT JOIN. I added that extra covering index between tags\_data and companies. So now I have 2 on there: company\_id, tag\_id and tag\_id, company\_id But the query takes about the same time. Here’s an updated EXPLAIN output after adding that 2nd index, but all still as LEFT JOINS as before: id select\_type table type possible\_keys key key\_len ref rows Extra1 SIMPLE contacts ref IX\_subscribed IX\_subscribed 15 const 1103 Using where; Using temporary; Using filesort1 SIMPLE companies ref PRIMARY,company\_id\_geo company\_id\_geo 4 dwcintranet.contacts.company\_id 1 1 SIMPLE tags\_data index company\_id,companies\_tags tags\_companies 26 NULL 41930 Using index1 SIMPLE tags eq\_ref PRIMARY,tag\_id\_name PRIMARY 4 dwcintranet.tags\_data.tag\_id 1 Any idea on what else I can try indexing or changing? Would it be quicker to use subquery/subselect instead of 3 joins? I’ve never tried using those before so not sure if that’s possible. Thanks for all your help so far! Cheers, B ---
**Author:** ![vgatto](https://sea1.discourse-cdn.com/flex019/user_avatar/forums.percona.com/vgatto/32/748_2.png) [@vgatto](https://forums.percona.com/u/vgatto)\ **Post date:** [November 24, 2008, 6:16pm UTC](https://forums.percona.com/t/3-way-left-join-with-group-concat-eliminate-using-filesort/1009/6 "2008-11-24T18:16:29Z")
I’d be curious to see the results of this: EXPLAIN SELECT companies.company\_name, tags\_data.\*FROM companies LEFT JOIN tags\_data USING(company\_id) If the plan for that query chooses the (tag\_id, company\_id) index, then I’d try running ANALYZE TABLE to make sure the index statistics are up to date. ---
**Author:** ![batfastad](https://avatars.discourse-cdn.com/v4/letter/b/df788c/32.png) [@batfastad](https://forums.percona.com/u/batfastad)\ **Post date:** [November 25, 2008, 7:17am UTC](https://forums.percona.com/t/3-way-left-join-with-group-concat-eliminate-using-filesort/1009/7 "2008-11-25T07:17:41Z")
I ran that EXPLAIN and here’s the output: id select\_type table type possible\_keys key key\_len ref rows Extra1 SIMPLE companies ALL NULL NULL NULL NULL 36557 1 SIMPLE tags\_data ALL company\_id,companies\_tags NULL NULL NULL 41930 So it looks like it’s using the company\_id,tag\_id index (companies\_tags). ---
**Author:** ![vgatto](https://sea1.discourse-cdn.com/flex019/user_avatar/forums.percona.com/vgatto/32/748_2.png) [@vgatto](https://forums.percona.com/u/vgatto)\ **Post date:** [November 25, 2008, 10:52am UTC](https://forums.percona.com/t/3-way-left-join-with-group-concat-eliminate-using-filesort/1009/8 "2008-11-25T10:52:27Z")
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 `contacts`LEFT 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.