EmergencyEMERGENCY? Get 24/7 Help Now!

The power of MySQL GROUP_CONCAT

 | October 22, 2013 |  Posted In: Insight for DBAs, MySQL

PREVIOUS POST
NEXT POST

MySQL GROUP_CONCATIn the very early days of Percona, Vadim wrote very nice post about MySQL GROUP_CONCAT (GROUP_CONCAT).

But I want to show you a bit more about it.

When is MySQL GROUP_CONCAT useful? Usually while working with Support customers I recommend it when you have aggregation of many-to-many info. It makes the view simpler and more beautiful and it doesn’t need much effort to make it work.

The following are some simple examples.

This is a test table:

Without grouping info the only way you can check things is:

But it looks much better and easier to read with GROUP_CONCAT:

Easy? Let’s go to production usage and some “real” examples 🙂

Assume you have 4 Support Engineers who were working with 6 Customers this week on 15 issues.

As it usually happens: everyone (sure, except those who are on vacation :)) worked on everything with everybody.

How you would represent it?

Here is my way:

Create test tables:

  • engineers (id, name, surname, URL) – list of engineers
  • customers (id, company name, URL) – list of customers
  • issues (id, customer_id, description) – list of issues assigned to customers
  • workflow (id, engineer_id, issue_id) – list of actions: issues and engineers who worked on them

Examples:

List of issues for each engineer (GROUP_CONCAT):

List of engineers for each customer (GROUP_CONCAT inside of GROUP_CONCAT):

PHP/HTML? Why not? It’s easy 🙂

Source Code:

“; foreach($rows as $row) { echo “‘.$row[“company”].”.$row[“description”].”.$row[“engineer_list”].’ ‘; } echo ”

“.$row[“id”].’

“; $result->close(); $mysqli->close(); ?>

Result:

1 OT Fix replication Miguel Nieto
2 PZ Help with installation of Percona Cluster Michael Rikmas
3 VK Hardware suggestions Marcos Albe, Michael Rikmas
4 FD Error: no space left Marcos Albe, Michael Rikmas, Miguel Nieto, Valerii Kravchuk
5 AS Help with setup daily backup by Xtrabackup Marcos Albe, Miguel Nieto, Valerii Kravchuk
6 SS Poke sales about Support agreement renewal Marcos Albe
7 FD Add more accounts for customer Miguel Nieto, Valerii Kravchuk
8 PZ Create Hot Fix of Bug 1040735 Marcos Albe, Michael Rikmas
9 OT Query optimisation Marcos Albe, Miguel Nieto
10 OT Prepare custom build for Solaris Miguel Nieto, Valerii Kravchuk
11 PZ explain about Percona Monitoring plugins Valerii Kravchuk
12 SS Prepare access for customer servers for future work Marcos Albe
13 AS Decribe load balancing for pt-online-schema-change Marcos Albe
14 FD Managing deadlocks Michael Rikmas, Valerii Kravchuk
15 OT Suggestions about buffer pool size Marcos Albe, Miguel Nieto

That’s a power of MySQL GROUP_CONCAT!

PREVIOUS POST
NEXT POST
Michael Rikmas

Michael joined Percona in October 2007. He serves in several roles, including Persona's 24x7 support coverage. He has an undergraduate degree in computer science, and in 2010 he started pursuing studies to earn an MBA.

22 Comments

  • Always worth remembering to set the session variable group_concat_max_len to a higher number if you are grouping excessively long lists

  • One thing to watch out for with GROUP_CONCAT (and ORDER BY) is how it can result in tmp tables on disk, the example query to list issues for each engineer above causes a tmp table on disk to be created:

    show session status like ‘Created_tmp_disk_tables’;
    +————————-+——-+
    | Variable_name | Value |
    +————————-+——-+
    | Created_tmp_disk_tables | 1 |
    +————————-+——-+

    set session group_concat_max_len=512;

    Stops the tmp table being created on disk. So does using ORDER BY NULL or removing the ORDER BY. Tested with mariadb 5.3.12, 10.0.4 and 5.6.13-rel61.0 Percona Server with XtraDB.

    http://dev.mysql.com/doc/refman/5.5/en/group-by-functions.html#function_group-concat

    The result type is TEXT or BLOB unless group_concat_max_len is less than or equal to 512, in which case the result type is VARCHAR or VARBINARY.

    http://dev.mysql.com/doc/refman/5.5/en/internal-temporary-tables.html

    Some conditions prevent the use of an in-memory temporary table, in which case the server uses an on-disk table instead:

    Presence of a BLOB or TEXT column in the table

    Presence of any string column in a GROUP BY or DISTINCT clause larger than 512 bytes

    Presence of any string column with a maximum length larger than 512 (bytes for binary strings, characters for nonbinary strings) in the SELECT list, if UNION or UNION ALL is used

    Also see:

    http://bugs.mysql.com/bug.php?id=14169 – When using GROUP_CONCAT() function with group_concat_max_len > 512 then the field type will be BLOB if ORDER BY is used, otherwise it will be VARCHAR.

    http://www.mysqlperformanceblog.com/2007/08/16/how-much-overhead-is-caused-by-on-disk-temporary-tables/

  • whats the max length i can set for ” SET SESSION group_concat_max_len=15000000; ” is there any limit for the variable group_concat_max_len ?

    thanks in advance.

  • Shamin,

    Manual says it’s:
    for 32-bit systems: 4294967295
    for 64-bit systems: 18446744073709547520

    You can see details here:
    https://dev.mysql.com/doc/refman/5.5/en/server-system-variables.html#sysvar_group_concat_max_len

  • Thank you for the article.
    I ‘ve got a question. Why are you using multiple table names in FROM and conditions in WHERE, instead of JOINs?

  • What about this scenario?

    JobNum OprSeq PartNum
    10000 10 N277070
    10000 10 N277070-MM
    10000 14 N277070
    10000 14 N277070-MM
    10000 14 N277070-001
    10000 16 N277070-MM
    10000 16 N277070
    10000 16 N277070-001
    10000 16 N277070-00024
    10000 20 N277070

    –EXPECTED RESULT–

    JobNum OprSeq PartNum PartNumItemTwo PartNumItemThree PartNumItemFour
    10000 10 N277070 N272770-MM NULL NULL
    10000 14 N277070 N272770-MM N277070-001 NULL
    10000 16 N277070 N272770-MM N277070-001 N277070-00024
    10000 20 N277070 NULL NULL NULL

    Is there a possible way to achieve this? I mean, is it even logical a cliente is asking for this?

  • How about if you want to do something a bit smarter so that the results that did look like this

    5 (Marcos Albe, Miguel Nieto, Valerii Kravchuk), 13 (Marcos Albe)

    turn into

    5 (Marcos Albe, Miguel Nieto, Valerii Kravchuk) and 13 (Marcos Albe)

  • Great article! Being a Java guy learning to use MySQL, you really contributed in me building faith in this environment! Thank you!

Leave a Reply

 
 

Percona’s widely read Percona Data Performance blog highlights our expertise in enterprise-class software, support, consulting and managed services solutions for both MySQL® and MongoDB® across traditional and cloud-based platforms. The decades of experience represented by our consultants is found daily in numerous and relevant blog posts.

Besides specific database help, the blog also provides notices on upcoming events and webinars.
Want to get weekly updates listing the latest blog posts? Subscribe to our blog now! Submit your email address below and we’ll send you an update every Friday at 1pm ET.

No, thank you. Please do not ask me again.