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 »

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:

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`
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:

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    
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
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

Could I be experiencing this bug?
http://bugs.mysql.com/bug.php?id=31094

My version of MySQL (running on On Ubuntu Server 8.04):
mysql Ver 14.12 Distrib 5.0.51a, for debian-linux-gnu (i486) using readline 5.2

Cheers, B
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:Hi VladSun

Could I be experiencing this bug?
http://bugs.mysql.com/bug.php?id=31094

My version of MySQL (running on On Ubuntu Server 8.04):
mysql Ver 14.12 Distrib 5.0.51a, for debian-linux-gnu (i486) using readline 5.2

Cheers, B
Mine is also 5.0.51a ... so I don't think it is the case.
I must recreate your DB design on my SQL server in order to be more helpful, but I don't have the time to do this right 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

Success :D
I posted on comp.databases.mysql and someone noticed that I had an error in my table structure.

companies.company_id was INT(11)
tags_data.company_id was VARCHAR(6)

A while ago I did have company_id as VARCHAR(6) for compatibility with our old filemaker solution, but I changed and forgot to change it in tags_data1
Making that one change made the original query run at 0.12 seconds, rather than 32 seconds!!! :lol:

I do have a further question on this though, that I hope you can help out with.
I'd like to get only a sub-selection of the tags that are selected for a company in my comma-separated field.
So here's my fully working query:

Code: Select all

SELECT  `companies`.`company_name`, `companies`.`county_state`, `companies`.`geo`, `contacts`.`contact_name`, `contacts`.`geo_contact`, 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`.`mag_amd`='FREE'GROUP BY `contacts`.`contact_id`ORDER BY `companies`.`geo`, `companies`.`company_name`, `contacts`.`contact_name` ASC
I'm running the WHERE on contacts.mag_amd=FREE, rather than subscribed=1, because I need to test with returning up to 20,000 records. subscribed=1 only gives me 777 but I will be needing this query on larger datasets.

But in my returned column:

Code: Select all

GROUP_CONCAT(`tags`.`name` ORDER BY `tags`.`name` ASC SEPARATOR '|') AS `classifications`
I only want to have tags returned that have `tags`.`category`='CLASS'

Do you know the best/quickest way of doing that?


Thanks so much, B
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:companies.company_id was INT(11)
tags_data.company_id was VARCHAR(6)

A while ago I did have company_id as VARCHAR(6) for compatibility with our old filemaker solution, but I changed and forgot to change it in tags_data1
Making that one change made the original query run at 0.12 seconds, rather than 32 seconds!!! :lol:
:lol: :lol: Sorry, I should have noticed that.

batfastad wrote:But in my returned column:

Code: Select all

GROUP_CONCAT(`tags`.`name` ORDER BY `tags`.`name` ASC SEPARATOR '|') AS `classifications`
I only want to have tags returned that have `tags`.`category`='CLASS'
Try
[sql]LEFT JOIN `tags` ON `tags_data`.`tag_id`=`tags`.`tag_id` AND `tags`.`category`='CLASS'[/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 »

batfastad wrote:I'm running the WHERE on contacts.mag_amd=FREE, rather than subscribed=1, because I need to test with returning up to 20,000 records. subscribed=1 only gives me 777 but I will be needing this query on larger datasets.
MySQL does not support partial indices.
Too bad for MySQL ;)
For large DBs you should avoid using char type fields in every clause, except SELECT.
That also applies to `tags`.`category`='CLASS' condition you want to have.
If you really need char type field in conditions, try using constant length char type (i.e. char(100)) instead of varchar/text etc. types.
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::lol: :lol: Sorry, I should have noticed that.
Hey no problem! It's me who should have noticed that!!!
I assumed my tables were all set up correctly, and they weren't.

It's amazing how much difference that makes though. Now I'm getting into more advanced queries, it shows how much work needs to go in to analysing queries and making them run at optimal speeds. I've still got loads to learn it seems :roll:
But I was worried for a while that maybe I'd made a mistake switching to SQL, that it wouldn't be able to do what I wanted after all. Well it can, and with insane performance!!
VladSun wrote: Try
[sql]LEFT JOIN `tags` ON `tags_data`.`tag_id`=`tags`.`tag_id` AND `tags`.`category`='CLASS'[/sql]
That's brilliant. Works perfectly! :D
Takes about 2 seconds to return 12,000 contacts records. And in tags_data there's about 20,000 records where tags.category=CLASS

I will eventually change the tags.category field into tags.category_id and have a separate table for storing the various tags_categories. Then it's all completely normalised and proper. And should keep it quick for when our data gets bigger.

Ok final final thing! :P
In my query, I'm returning contacts.geo_contact and companies.geo
geo is the ISO 3166 2 letter country code for that country.

I have a table called countries which has 2 fields: geo and country, where country is the full name for the country.
How can I add those joins so that instead of contacts.geo_contact and companies.geo, I'm getting the full country name instead from countries.country?
So within my query, I need to join contacts.geo_contact to countries.geo, and companies.geo to countries.geo somehow
But I'm not sure what place in my query I need to add those joins?

Thanks so much, B
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:Ok final final thing! :P
In my query, I'm returning contacts.geo_contact and companies.geo
geo is the ISO 3166 2 letter country code for that country.

I have a table called countries which has 2 fields: geo and country, where country is the full name for the country.
How can I add those joins so that instead of contacts.geo_contact and companies.geo, I'm getting the full country name instead from countries.country?
So within my query, I need to join contacts.geo_contact to countries.geo, and companies.geo to countries.geo somehow
But I'm not sure what place in my query I need to add those joins?
Try it - trying it doesn't hurt you, right? ;) I'm pretty sure you can do this by yourself.
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:Try it - trying it doesn't hurt you, right? ;) I'm pretty sure you can do this by yourself.
:lol: Nope can't work it out :(
I've tried adding new LEFT JOINS before and after the existing LEFT JOINS, but that doesn't work. Also tried:

Code: Select all

WHERE `contacts`.`geo_contact`=`countries`.`iso2`AND `countries` ON `companies`.`geo`=`countries`.`iso2`AND `contacts`.`mag_amd`='FREE'
But after that, I'm out of ideas

Could you give me a hint? ;)
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:

Code: Select all

WHERE `contacts`.`geo_contact`=`countries`.`iso2`AND `countries` ON `companies`.`geo`=`countries`.`iso2`AND `contacts`.`mag_amd`='FREE'
Post full query - the SQL you've posted seems ... strange ;)

I think you should add an ID column in your countries table and perfom JOIN by this column instead of char type column ;)
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 »

Code: Select all

SELECT  `companies`.`company_name`, `companies`.`county_state`, `companies`.`geo`, `contacts`.`contact_name`, `contacts`.`geo_contact`,  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` AND `tags`.`category`='CLASS' WHERE `contacts`.`geo_contact`=`countries`.`iso2`AND `countries` ON `companies`.`geo`=`countries`.`iso2` AND `contacts`.`mag_amd`='FREE' GROUP BY `contacts`.`contact_id`ORDER BY `companies`.`geo`, `companies`.`company_name`, `contacts`.`contact_name` ASC
I get an error when I run the query. And I don't know how I would then access/output the data within the SELECT clause?
Cheers, B
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:

Code: Select all

WHERE `contacts`.`geo_contact`=`countries`.`iso2`AND `countries` ON `companies`.`geo`=`countries`.`iso2`AND `contacts`.`mag_amd`='FREE'
You can't use ON keyword in WHERE clause ;)
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 »

Code: Select all

SELECT  `companies`.`company_name`, `companies`.`county_state`, `companies`.`geo`, `contacts`.`contact_name`, `contacts`.`geo_contact`,  GROUP_CONCAT(`tags`.`name` ORDER BY `tags`.`name` ASC SEPARATOR ',') AS `classifications` FROM `contacts`, `countries`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` AND `tags`.`category`='CLASS' WHERE `contacts`.`geo_contact`=`countries`.`iso2`AND `companies`.`geo`=`countries`.`iso2` AND `contacts`.`mag_amd`='FREE' GROUP BY `contacts`.`contact_id`ORDER BY `companies`.`geo`, `companies`.`company_name`, `contacts`.`contact_name` ASC
Ah ok, I've changed it.
I've no idea if this is correct, it's the only other MySQL JOIN syntax I know, other than LEFT/RIGHT JOINS (which I can't work out where to put to get them to work). Also I can't work out how to select the country names to get them to output

Cheers, B
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 »

Ok I've solved this final country name hurdle, but not through MySQL.
I did a separate query and built an array of the geo and country names, then just used that when I was looping through the MySQL data. Not pretty, but it works.

Is there a way I could do these final 2 joins in MySQL to get the country names?
I just can't figure it out :(

Cheers, B
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's absolutely the same as the JOINs you use. I can't undertsand what troubles may you have ;)
There are 10 types of people in this world, those who understand binary and those who don't
Post Reply