Search code examples
mysqlsqlquery-optimization

Is using a Subquery is the best way to use GROUP_CONCAT() on none-related table?

In this Query

SELECT username, (SELECT GROUP_CONCAT(color) FROM colors) FROM users WHERE id = 1;

Is this the best and only way to get all the colors from the table colors? I can't figure out a way to INDEX the colors table to make it fast, So I have my concerns.

users: id(int(11), Primary), username(varchar(12), Unique)
colors: id(int(11), Primary), color(varchar(12), Unique)

Sample: http://sqlfiddle.com/#!9/064c34/2/0

Solution

  • Your query is basically the only option.

    You could move the subquery to the FROM clause. That would guarantee that it gets executed only once. However, you are selecting only one row, so it should be executed only once.

    Why do you think that the query is slow? Given that the default length for the group_concat() is 1,024 characters, you cannot be aggregating that many values together.

    Well, there is another option:

    select u.*, c.colors
from users u cross join
     (select @c as colors
      from (select @c := concat(@c, ',', c.color)
            from colors c cross join
                 (select @c := '') params
           ) c
      limit 1
     ) c
where u.id = 1;

    I would be surprised if this provided better performance.