MySQL, get joined records separated by commas

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

User avatar
batfastad
Forum Contributor
Posts: 433
Joined: Tue Mar 30, 2004 4:24 am
Location: London, UK

Re: MySQL, get joined records separated by commas

Post by batfastad »

I changed company_name to varchar, recreated an index, and still took 32s to execute.
Tried removing the ORDER BY clause altogether, still took 32s!

Just tried that query you posted, and again it took between 31.0 and 31.6 seconds :(

EDIT: Also just tried removing the ORDER BY within the GROUP CONCAT() function, and still 31-32 seconds

Thanks, Ben
User avatar
VladSun
DevNet Master
Posts: 4313
Joined: Wed Jun 27, 2007 9:44 am
Location: Sofia, Bulgaria

Re: MySQL, get joined records separated by commas

Post by VladSun »

The output of:
[sql]EXPLAINSELECT `contacts`.`contact_name`, `companies`.`company_name`, GROUP_CONCAT(`tags`.`name` ORDER BY `tags`.`name` ASC SEPARATOR '|') AS `classifications`FROM `contacts`    force INDEX (PRIMARY, `contact_name`, `company_id`, `IX_subscribed`)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`='1'GROUP BY `contacts`.`contact_id`LIMIT 0, 10000[/sql]
There are 10 types of people in this world, those who understand binary and those who don't
User avatar
VladSun
DevNet Master
Posts: 4313
Joined: Wed Jun 27, 2007 9:44 am
Location: Sofia, Bulgaria

Re: MySQL, get joined records separated by commas

Post by VladSun »

Btw, how do you measure the execution time?
If you put
[sql]LIMIT 1[/sql] instead of
[sql]LIMIT 0, 10000[/sql]?
There are 10 types of people in this world, those who understand binary and those who don't
User avatar
batfastad
Forum Contributor
Posts: 433
Joined: Tue Mar 30, 2004 4:24 am
Location: London, UK

Re: MySQL, get joined records separated by commas

Post by batfastad »

The output from phpMyAdmin for that EXPLAIN query...

Code: Select all

id  select_type     table   type    possible_keys   key     key_len     ref     rows    Extra
1   SIMPLE  contacts    ref     IX_subscribed   IX_subscribed   15  const   1103    Using where; Using temporary; Using filesort
1   SIMPLE  companies   eq_ref  PRIMARY     PRIMARY     4   dwcintranet.contacts.company_id     1    
1   SIMPLE  tags_data   index   company_id,companies_tags   companies_tags  26  NULL    41930   Using index
1   SIMPLE  tags    eq_ref  PRIMARY     PRIMARY     4   dwcintranet.tags_data.tag_id    1    
User avatar
VladSun
DevNet Master
Posts: 4313
Joined: Wed Jun 27, 2007 9:44 am
Location: Sofia, Bulgaria

Re: MySQL, get joined records separated by commas

Post by VladSun »

[sql]EXPLAINSELECT `contacts`.`contact_name`, `companies`.`company_name`, GROUP_CONCAT(`tags`.`name` ORDER BY `tags`.`name` ASC SEPARATOR '|') AS `classifications`FROM `contacts`    force INDEX (PRIMARY, `contact_name`, `company_id`)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`GROUP BY `contacts`.`contact_id`[/sql]?
There are 10 types of people in this world, those who understand binary and those who don't
User avatar
batfastad
Forum Contributor
Posts: 433
Joined: Tue Mar 30, 2004 4:24 am
Location: London, UK

Re: MySQL, get joined records separated by commas

Post by batfastad »

VladSun wrote:Btw, how do you measure the execution time?
If you put
[sql]LIMIT 1[/sql] instead of
[sql]LIMIT 0, 10000[/sql]?
I'm going off phpMyAdmin's value of execution time... Query took 31.1066 sec
If I change it to LIMIT 1 or LIMIT 0, 1000000000 instead of 0, 10000 it makes no difference.

Here's the 2nd EXPLAIN output:

Code: Select all

id  select_type     table   type    possible_keys   key     key_len     ref     rows    Extra
1   SIMPLE  contacts    ALL     NULL    NULL    NULL    NULL    37639   Using temporary; Using filesort
1   SIMPLE  companies   eq_ref  PRIMARY     PRIMARY     4   dwcintranet.contacts.company_id     1    
1   SIMPLE  tags_data   index   company_id,companies_tags   companies_tags  26  NULL    41930   Using index
1   SIMPLE  tags    eq_ref  PRIMARY     PRIMARY     4   dwcintranet.tags_data.tag_id    1    
User avatar
VladSun
DevNet Master
Posts: 4313
Joined: Wed Jun 27, 2007 9:44 am
Location: Sofia, Bulgaria

Re: MySQL, get joined records separated by commas

Post by VladSun »

WTH this "Using filesort" comes from :(
That's why I put the "force INDEX (PRIMARY, `contact_name`, `company_id`)"
The battle is to remove the Using filesort part of EXPLAIN output.
Try to build your query in such way that the Using filesort is missing...

I have a DB structure very similar to yours:

Code: Select all

EXPLAINSELECT       `user`.id,       `user`.`username`,       GROUP_CONCAT(`group`.name ORDER BY `group`.`name` ASC SEPARATOR '|')FROM    `user`force INDEX (PRIMARY,IX_username)LEFT JOIN    `level` ON                `user`.FK_level_id = `level`.idLEFT JOIN    `user_group` ON                `user_group`.FK_user_id = `user`.idLEFT JOIN    `group` ON           `group`.id = `user_group`.FK_group_idGROUP BY     `user`.id
=>

Code: Select all

1;"SIMPLE";"user";"index";"";"PRIMARY";"4";"";200000;""
1;"SIMPLE";"level";"eq_ref";"PRIMARY";"PRIMARY";"4";"wargame2.user.FK_level_id";1;"Using index"
1;"SIMPLE";"user_group";"ref";"PRIMARY,IX_user_id";"PRIMARY";"4";"wargame2.user.id";2;"Using index"
1;"SIMPLE";"group";"eq_ref";"PRIMARY";"PRIMARY";"4";"wargame2.user_group.FK_group_id";1;""
 
Notice the 200000 rows I have in `user`.
Execution time: .... 0.02 sec.

So, I am sure your query can be optimized.
Last edited by VladSun on Tue Aug 03, 2010 4:13 am, edited 1 time in total.
There are 10 types of people in this world, those who understand binary and those who don't
User avatar
VladSun
DevNet Master
Posts: 4313
Joined: Wed Jun 27, 2007 9:44 am
Location: Sofia, Bulgaria

Re: MySQL, get joined records separated by commas

Post by VladSun »

Compare
[sql]EXPLAINSELECT `contacts`.`contact_name`, `contacts`.`contact_id`FROM `contacts`GROUP BY `contacts`.`contact_id` [/sql]

[sql]EXPLAINSELECT `contacts`.`contact_name`, `contacts`.`contact_id`FROM `contacts`    force INDEX (PRIMARY)GROUP BY `contacts`.`contact_id`[/sql]
There are 10 types of people in this world, those who understand binary and those who don't
User avatar
batfastad
Forum Contributor
Posts: 433
Joined: Tue Mar 30, 2004 4:24 am
Location: London, UK

Re: MySQL, get joined records separated by commas

Post by batfastad »

Hi VladSun
Ok, it's good to know that a 3 way join with GROUP CONCAT should be running faster than mine is!

Could it be down to the storage engine? I'm using MyISAM, I've always assumed that was the standard storage engine to use.

Or could my query syntax be improved?
Looking through this page http://www.wellho.net/solutions/mysql-l ... ables.html
I found this example of a 3-way LEFT JOIN:

Code: Select all

SELECT * FROM (bdg LEFT JOIN res on bdg.bid = res.bid) LEFT JOIN dom on res.rid = dom.rid;
Which is different to the way I've written mine.

Ok onto the comparison you asked for.

1st query:

Code: Select all

id  select_type     table   type    possible_keys   key     key_len     ref     rows    Extra
1   SIMPLE  contacts    ALL     NULL    NULL    NULL    NULL    37639   Using filesort
2nd query:

Code: Select all

id  select_type     table   type    possible_keys   key     key_len     ref     rows    Extra
1   SIMPLE  contacts    index   NULL    PRIMARY     4   NULL    37639    
Looks like that 1st one has "Using filesort"
User avatar
Eran
DevNet Master
Posts: 3549
Joined: Fri Jan 18, 2008 12:36 am
Location: Israel, ME

Re: MySQL, get joined records separated by commas

Post by Eran »

Try running ANALYZE and OPTIMIZE on the contacts table
User avatar
VladSun
DevNet Master
Posts: 4313
Joined: Wed Jun 27, 2007 9:44 am
Location: Sofia, Bulgaria

Re: MySQL, get joined records separated by commas

Post by VladSun »

It would rarely help:
[sql]analyze TABLE `user`;OPTIMIZE TABLE `user`;EXPLAINSELECT      `user`.id, `user`.usernameFROM     `user`GROUP BY     `user`.id [/sql]
=>

Code: Select all

1;"SIMPLE";"user";"ALL";"";"";"";"";200000;"Using filesort"
While:
[sql]EXPLAINSELECT      `user`.id, `user`.usernameFROM     `user`    force INDEX (PRIMARY)GROUP BY     `user`.id [/sql]
=>

Code: Select all

1;"SIMPLE";"user";"index";"";"PRIMARY";"4";"";200000;""
There are 10 types of people in this world, those who understand binary and those who don't
User avatar
VladSun
DevNet Master
Posts: 4313
Joined: Wed Jun 27, 2007 9:44 am
Location: Sofia, Bulgaria

Re: MySQL, get joined records separated by commas

Post by VladSun »

I think your query is just fine - we need to play wth indexies and force MySQL to use them.

Begining with
[sql]EXPLAINSELECT `contacts`.`contact_name`, `contacts`.`contact_id`FROM `contacts`    force INDEX (PRIMARY)GROUP BY `contacts`.`contact_id`[/sql]try adding the rest of your SQL lines one by one and look for the "Using filesort" F%#$%$#@%G string :)
There are 10 types of people in this world, those who understand binary and those who don't
User avatar
VladSun
DevNet Master
Posts: 4313
Joined: Wed Jun 27, 2007 9:44 am
Location: Sofia, Bulgaria

Re: MySQL, get joined records separated by commas

Post by VladSun »

Did you try it?

PS: It's funny, because my PGSQL queries, which are far more complicated than these ones (6+ joins, even subselects), run in 10 secs on million+ records tables. I prefer PGSQL over MySQL, but I prefer it even more now ;)
There are 10 types of people in this world, those who understand binary and those who don't
User avatar
batfastad
Forum Contributor
Posts: 433
Joined: Tue Mar 30, 2004 4:24 am
Location: London, UK

Re: MySQL, get joined records separated by commas

Post by batfastad »

Hi VladSun
It's re-assuring to know that even far more complicated queries can be done faster!
I've had a chance to try it now. I started off with the base query as you suggested.

First I added the WHERE

Code: Select all

WHERE `contacts`.`subscribed`='VIP'
When I did EXPLAIN, no filesort

Added the first join, and also `companies`.`company_name` to the select

Code: Select all

LEFT JOIN `companies` USING(`company_id`)
No filesort

Added the second join: LEFT JOIN `tags_data` ON `companies`.`company_id` = `tags_data`.`company_id`
So my query became:

Code: Select all

SELECT `contacts`.`contact_name` , `contacts`.`contact_id` , `companies`.`company_name`FROM `contacts`FORCE INDEX (PRIMARY )LEFT JOIN `companies`USING ( `company_id` )LEFT JOIN `tags_data` ON `companies`.`company_id` = `tags_data`.`company_id`WHERE `contacts`.`subscribed` = 'VIP'GROUP BY `contacts`.`contact_id`
And using filesort appeared in my EXPLAIN output!

Code: Select all

+----+-------------+-----------+--------+---------------------------+------------+---------+---------------------------------+-------+----------------------------------------------+
| id | select_type | table     | type   | possible_keys             | key        | key_len | ref                             | rows  | Extra                                        |
+----+-------------+-----------+--------+---------------------------+------------+---------+---------------------------------+-------+----------------------------------------------+
|  1 | SIMPLE      | contacts  | ALL    | NULL                      | NULL       | NULL    | NULL                            | 37639 | Using where; Using temporary; Using filesort |
|  1 | SIMPLE      | companies | eq_ref | PRIMARY                   | PRIMARY    | 4       | dwcintranet.contacts.company_id |     1 |                                              |
|  1 | SIMPLE      | tags_data | index  | company_id,companies_tags | company_id | 21      | NULL                            | 41930 | Using index                                  |
So it's something to do with that 2nd join between tags_data.company_id

One of the indexes I have in tags_data is called companies_tags which is on 2 columns, tags_data.company_id and tags_data.tag_id
Adding this index was the one that cut my query down from 82 seconds to the 32 second mark earlier in our discussion.

I tried changing the order of this index, so it was tags_data.tag_id and tags_data.company_id but it made no difference, still using filesort.
The query was a bit quicker: 27 seconds, but I guess that's because the rest of the query is missing
User avatar
VladSun
DevNet Master
Posts: 4313
Joined: Wed Jun 27, 2007 9:44 am
Location: Sofia, Bulgaria

Re: MySQL, get joined records separated by commas

Post by VladSun »

batfastad wrote:Added the second join: LEFT JOIN `tags_data` ON `companies`.`company_id` = `tags_data`.`company_id`
So my query became:

Code: Select all

SELECT `contacts`.`contact_name` , `contacts`.`contact_id` , `companies`.`company_name`FROM `contacts`FORCE INDEX (PRIMARY )LEFT JOIN `companies`USING ( `company_id` )LEFT JOIN `tags_data` ON `companies`.`company_id` = `tags_data`.`company_id`WHERE `contacts`.`subscribed` = 'VIP'GROUP BY `contacts`.`contact_id`
And using filesort appeared in my EXPLAIN output!
Try forcing other indexies. E.g.:
[sql]FORCE INDEX (PRIMARY, IX_company_name)[/sql]
OR
[sql]...LEFT JOIN `companies`FORCE INDEX (PRIMARY)USING ( `company_id` )...[/sql]
etc., till you find the one you need to remove the filesort operation.
There are 10 types of people in this world, those who understand binary and those who don't
Post Reply