Page 2 of 4

Re: MySQL, get joined records separated by commas

Posted: Fri Nov 21, 2008 8:12 am
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

Re: MySQL, get joined records separated by commas

Posted: Fri Nov 21, 2008 8:18 am
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]

Re: MySQL, get joined records separated by commas

Posted: Fri Nov 21, 2008 8:20 am
by VladSun
Btw, how do you measure the execution time?
If you put
[sql]LIMIT 1[/sql] instead of
[sql]LIMIT 0, 10000[/sql]?

Re: MySQL, get joined records separated by commas

Posted: Fri Nov 21, 2008 8:23 am
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    

Re: MySQL, get joined records separated by commas

Posted: Fri Nov 21, 2008 8:27 am
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]?

Re: MySQL, get joined records separated by commas

Posted: Fri Nov 21, 2008 8:40 am
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    

Re: MySQL, get joined records separated by commas

Posted: Fri Nov 21, 2008 9:07 am
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.

Re: MySQL, get joined records separated by commas

Posted: Fri Nov 21, 2008 9:35 am
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]

Re: MySQL, get joined records separated by commas

Posted: Fri Nov 21, 2008 9:43 am
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"

Re: MySQL, get joined records separated by commas

Posted: Fri Nov 21, 2008 9:43 am
by Eran
Try running ANALYZE and OPTIMIZE on the contacts table

Re: MySQL, get joined records separated by commas

Posted: Fri Nov 21, 2008 9:50 am
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;""

Re: MySQL, get joined records separated by commas

Posted: Fri Nov 21, 2008 9:53 am
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 :)

Re: MySQL, get joined records separated by commas

Posted: Fri Nov 21, 2008 8:28 pm
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 ;)

Re: MySQL, get joined records separated by commas

Posted: Sat Nov 22, 2008 3:15 am
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

Re: MySQL, get joined records separated by commas

Posted: Sat Nov 22, 2008 12:13 pm
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.