3

I have mysql 5.6 installed.

I have a query that uses group_concat to concatenate a list of user ids into one string with ','. i need to increase the limit of group_concat_max_len to 500000.

will that cause a performance issue? why group_concat is limited in the first place ? should I query each line and concat it on server ?

any information regarding the issue would be greatly appreciated.

ufk
  • 30,912
  • 70
  • 235
  • 386
  • There is no question concerning relational databases for which GROUP_CONCAT (or CONCAT) need form any part of the answer. So I think you really need to ask yourself if you're approaching the real problem the right way. You might also ask yourself why the variable isn't set to a billion, or whatever, by default. – Strawberry Nov 18 '16 at 11:35
  • 1
    @Strawberry - it seems you are correct :) the group_concat_max_len var isn't big for a reason. performance wise, I broke the query to two queries, one that gives me all the data, and the other that gives the relevant rows that I would previously fetch with group_concat. please post your answer so i'll mark it. thanks – ufk Nov 27 '16 at 12:31
  • You're allowed to answer your own question. SO positively encourages it. http://stackoverflow.com/help/self-answer – Strawberry Nov 27 '16 at 12:33

1 Answers1

2

the documentation states that 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.

so increasing group_concat in so many percentage doesn't seem like the right call.. first, it means changing other system variables that my affect the system in different ways

besides that, it's a very big project that has only one group_concat query, so I don't want to modify other system variables only for one specific query.

and in general what @Strawberry said is correct, the value is set to 1024 by default. probably for a good reason.

so I divided my query to two queries..

the first, returns all the data that I need besides the group_concat column

the second, returns all the relevant rows that I used to group_cocat.

ufk
  • 30,912
  • 70
  • 235
  • 386