Working with group_concat

Question

Working with group_concat

Navigation

#1 by (4 votes)

3

I have some questions regarding the use of group_concat , more specifically performance. When using group_concat you can not 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 would like to know if group_concat somehow impairs performance, since it returns all ID's - as in the example above - and the reason limit is ignored in the query. p>

mysql sql query group-concat

asked by anonymous 18.08.2015 / 00:53

1 answer

Unable to create managed bean How do I know if an Image View already has an image?

score 4 · Accepted Answer

Group_concat concatenates a sequence of fields according to your defined query and group.

In your example, there is no group defined so it will concatenate all the id of the table table. The limit clause is enforced after select is run. That is, MySQL / MariaDB will go through the entire table, concatenate all fields, and bring the first 5 groups (in this case, there will be only 1).

For what you have described, this is not what you want.

Your performance issue should be due to the full-table scan triggered.

Among some solutions, you can:

Use a where clause
Use a subselect with the limit, and apply the group_concat after.

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

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

set @@group_concat_max_len=1048576;

Remembering that this value is valid only for the duration of your session.