MySQL Forums
Forum List  »  Performance

Setting group_concat_max_len
Posted by: Rob Reid
Date: May 25, 2010 04:00PM

I am rewriting a plugin that was causing me PHP out of memory errors and doing hundreds of unnecessary SQL calls in a loop. The plugin builds an XML file and on each iteration through a recordset it was building up a string (XML) and calling a couple of other functions that made their own DB calls.

I have replaced all this with one SQL statement so that I am only doing one call to the DB whether I have one record or hundred thousand records this has reduced the PHP memory consumption considerably (by moving the load to the DB). However to replace all the string building functionality I have had to use GROUP_CONCAT and CONCAT functions to build the string together combining each row in the recordset.

I am pretty new to MySQL having spend my years developing MSSQL so I was scratching my head for a while wondering why my XML was broken off after the first 1000 chars or so. It was only then I discovered the group_concat_max_len setting and I am currently having to set the value to an arbitrary high value first before running my SQL e,g

SET SESSION group_concat_max_len = 10485760;

But I would like to be able to set it to a more relevant value depending on the data I am bringing back. What is the best way of calculating this value without having to do a COUNT and then multiplying it by the LEN of the columns I am appending together?

I don't want to set the value too high for sites with a few records but then again for sites with thousands of records I don't want it set too small and have the XML cut off at some point.

I have also heard from someone that MySQL is not very good at string concatenation and that using GROUP_CONCAT or CONCAT on too many records or with too long strings will cause problems.

SO far I have not had any issues and my own site has 8000+ records being returned in the XML. The size of the XML string added per record does not differ that much as the data being returned is a standard format and a rough analysis says on average each record/string is between 250 and 400 ASCII characters long.

So my questions are:

Are there problems with MySQL and its CONCAT methods?

Are there problems with the group_concat_max_len setting?

What's the best way of setting this value? Just giving everyone a very high setting or doing a COUNT(*) first and then multiplying it by 400?

Also I am trying to find a Memory counter so that I can test the amount of SQL memory used by my query which returns instantly. Using TOP just gives me the total MySQL process memory consumption whereas I want a specific value for Memory and CPU if possible for individual queries. Is this possible in MySQL?

Options: ReplyQuote

Written By
Setting group_concat_max_len
May 25, 2010 04:00PM
May 28, 2010 07:18PM
May 29, 2010 10:33PM
May 30, 2010 04:48PM
May 30, 2010 06:43PM
June 04, 2010 06:27AM
June 04, 2010 07:10AM
June 04, 2010 10:09AM

Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.