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.