Re: MySQL, get joined records separated by commas
Posted: Mon Nov 24, 2008 3:59 am
Hi VladSun
I was still trying this over the weekend but still no luck forcing any indexes. I can't think of any new indexes that might help either.
But I tried changing the query and going one step up the branch by trying to get the same data but just from companies, removing the final contacts stage.
Here's my new query:
And rather than searching contacts on subscribed=1, I'm searching company on the geo field (a 2 letter ISO country code) for ES which returns about 750 records. This query still takes about 30 seconds.
When I run this simpler query from the command line mysql I get 814 rows in set, and 1 warning.
What does the warning mean and is there any way to find out what it is?
When I do EXPLAIN:
I still get the "using filesort" which indicates the problem is somewhere between companies and tags_data, or tags_data and tags.
So there's definitely something wrong with this, as I have other queries with 2 LEFT JOINS on other tables that run in 0.0x seconds!
Any ideas?
I'm continuing to try various things through trial and error.
Cheers, B
I was still trying this over the weekend but still no luck forcing any indexes. I can't think of any new indexes that might help either.
But I tried changing the query and going one step up the branch by trying to get the same data but just from companies, removing the final contacts stage.
Here's my new query:
Code: Select all
SELECT `companies`.`company_id` , `companies`.`company_name`, `companies`.`geo`, GROUP_CONCAT(`tags`.`name` SEPARATOR '|') AS `classifications`FROM `companies`LEFT JOIN `tags_data` ON `companies`.`company_id` = `tags_data`.`company_id`LEFT JOIN `tags` ON `tags_data`.`tag_id` = `tags`.`tag_id`WHERE `companies`.`geo` = 'ES'GROUP BY `companies`.`company_id`When I run this simpler query from the command line mysql I get 814 rows in set, and 1 warning.
What does the warning mean and is there any way to find out what it is?
When I do EXPLAIN:
Code: Select all
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE companies ref geo geo 9 const 771 Using where; Using temporary; Using filesort
1 SIMPLE tags_data index company_id tags_companies 26 NULL 41930 Using index
1 SIMPLE tags eq_ref PRIMARY PRIMARY 4 dwcintranet.tags_data.tag_id 1 So there's definitely something wrong with this, as I have other queries with 2 LEFT JOINS on other tables that run in 0.0x seconds!
Any ideas?
I'm continuing to try various things through trial and error.
Cheers, B