MySQL performance: Single query using GROUP_CONCAT, or two separate queries?
group-concat, mysql, performance
Solution
The performance should be OK - better than two queries. You need to be aware that the length is limited though:
The result is truncated to the maximum length that is given by the group_concat_max_len system variable, which has a default value of 1024. The value can be set higher, although the effective maximum length of the return value is constrained by the value of max_allowed_packet. The syntax to change the value of group_concat_max_len at runtime is as follows, where val is an unsigned integer:
SET [GLOBAL | SESSION] group_concat_max_len = val;
Problem
I have a MySQL database in which each user has an account, and each account can have multiple permissions. My ultimate goal is to end up with the account's username, and a comma-delimited list of permissions ids. There are two ways I can accomplish this: ``` SELECT a.username, GROUP_CONCAT(rp.permission_id) as permission_ids FROM account AS a JOIN role_permission AS rp ON rp.role_id = a.role_id WHERE a.id = 1902 ``` ... or ... ``` SELECT username FROM account WHERE id = 1902; SELECT permission_id FROM account_permission WHERE account_id = 1902 ``` With the single query, I get my results exactly as I want them. With two queries, I have to create the comma-delimited list in the app (PHP) using the second result set. Are there any performance reasons to NOT choose the first option? I have never used GROUP_CONCAT before, so I don't know the implications of it, performance-wise.