Mysql GROUP_CONCAT Magic

---

A very complex query I used stopped working after upgrading to mysql 5 so I had to rewrite it. To simplify it, it had to return a lot of rows, but for each a few types and users too. Like below, but with a bit more rows:

Code:

key1 date1 (userX userY userZ) (typeX typeY) key2 date2 (userY) (typeX typeZ)

Both the types and the users were in a different table, together with the key of the row they related to.

Now of course the easiest thing would be to query for the rows and then per row query for the users and for the types. But for 100 rows (let alone 1000), that would relate to ~200 queries - not a good idea.

I used to have 3 queries, one for the rows, one for the users and one for the types, sorted the same way and then merged together with php. Quite a hard script and query (unbelievably long), but it worked and wasn't too slow. At least until mysql 5.

But luckily I found this nice mysql command: GROUP_CONCAT. It can output the results of a query inside a query, together with some formatting of the data. Unfortunately the formatting has to be done inside mysql so php functions can't be used, the same as existing templates.

So the query became: (simplified, of course)

Code:

$sql = " SELECT row.*, GROUP_CONCAT(DISTINCT '<b>', users.username, '</b>' SEPARATOR '<br>') AS users FROM row LEFT JOIN users ON users.id = row.id WHERE <where statements> GROUP BY id ";

Of course with also the types, but the same way as the users. I had to add the DISTINCT in the GROUP_CONCAT, because otherwise the users or types were repeated if there were more types or users. It outputs the rows, and in the 'users', a list of bold usernames, each on a separate line (due to the br between them).

There was only one problem left. The GROUP_CONCAT has a very low size limit and cuts the result there, resulting in broken html. The default maximum length is 1024. I used a query to enlarge this (8 times). Of course this might need to be bigger if you have a lot of output.

Code:

$sql = " SET SESSION group_concat_max_len = 8192 ";

And now everything is retrieved in 1 query. It's still pretty long, but way less complex and easy to understand/change (unlike before). And it seems fast. For over thousand rows, the page is loaded in 0.278 seconds, which is good for so much output.



20 blog comments below

Was old query used on the memberlist per chance?

Manofgames on Mon Sep 24, 2007 6:48 pm

I'm guessing this for a staff feature which I've recently noticed was working properly again

mathiaus on Mon Sep 24, 2007 7:04 pm

@Manofgames, no the memberlist is almost completely standard phpbb code. I removed it due to the link taking space and because it can be used for bad purposes (pm spamming, ...). Besides, the actual page is still working, only the link is currently gone. I'm thinking of making it available again, but not to all members (like more than 50 posts needed). I might also change the page a bit. It's just not a high priority.

@mathiaus, yes it's for the page with all the AWITS. The action page was also repaired, the FROM fields needed to be between parenthesis in order for the query to work (otherwise the LEFT JOIN caused a problem).

Bondings on Mon Sep 24, 2007 7:14 pm

Quote:

I'm thinking of making it available again, but not to all members (like more than 50 posts needed).

Thats a good idea. We need a quote button for the blogs

garionw on Tue Sep 25, 2007 9:55 am

Cool, but how do we put it on our site?

AK47BLAZE on Tue Jul 15, 2008 6:25 am

ya guys im having the same issue, how would i put this on my site????????????????????????????

knight30303 on Fri Jul 18, 2008 10:08 pm

Give this type of infomation in regular inerval
keep it up
very helpful

EshaAngel on Thu Aug 21, 2008 12:13 pm

hi..can anyone tell me what configurations we need to do in php so that it can be connected to mysql for exeuting queries.i only want to know the initial setups that we need to do

mr_accounts on Sun Apr 12, 2009 4:39 am

google ...

jealous494 on Tue Apr 21, 2009 9:47 am

Webmonkey?

eddiereynolds on Tue May 19, 2009 11:32 am

HTML Goodies

eddiereynolds on Tue May 19, 2009 11:32 am

Not sure what else to tell you.

eddiereynolds on Tue May 19, 2009 11:33 am

Not sure what else to tell you.

eddiereynolds on Tue May 19, 2009 11:33 am

Not sure what else to tell you.

eddiereynolds on Tue May 19, 2009 11:33 am

Could try google

eddiereynolds on Tue May 19, 2009 11:33 am

hahaha! cool

clutchdude on Wed May 20, 2009 6:05 pm

can nyone point out the difference between My_SQL and sqlplus..yea yea i knw different interfaces and all...but the basic differences in the implementation???

jais on Tue Jun 09, 2009 11:49 am

Was old query used on the memberlist per chance?

blackdog on Thu Jun 11, 2009 7:09 pm

i had a lot of prob. about "the query is too complex" .. that I have to rewrite the new sql statement.

leondevil on Tue Sep 08, 2009 8:24 am

nice work

shadesoul on Sun Oct 04, 2009 10:28 pm