Operation of group_concat

Question

I have some questions related to the use of group_concat, more specifically for performance. When using group_concat can’t use limit.

// a query retorna todos os ID's
select group_concat( id ) from table limit 5

// pelo que eu vi, a solução seria usar substring_index para pegar a quantidade X
substring_index( group_concat( id ) , ',' , 5 )

I was wondering if group_concat impairs performance in some way, since it returns all the ID’s - as in the example above - and the reason for limit be ignored in query.

Answer 1

group_concat concatenates a sequence of fields according to its defined query and group.

In your example, there is no set group, so it will concatenate all the id table table. The clause limit is applied after the select be executed. That is, Mysql/Mariadb will go through the entire table, concatenate all fields, and bring the first 5 groups (in case there will be only 1).

From what you described, this is not what you want.

Your performance problem must be due to the full-table scan triggered.

Among a few solutions, you can:

• Using a Where clause
• Use a subselect with limit, and apply group_concat after.

Just pointing out that by default group_concat has a size limit (1024 bytes).

You should adjust this limit before executing the query that brings group_concat with (increase to 1M):

set @@group_concat_max_len=1048576;

Remembering that this value is valid only while your session lasts.