MySQL Forums :: Performance :: Setting group_concat_max_len

Advanced Search

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

Subject Views Written By Posted
Setting group_concat_max_len 9547 Rob Reid 05/25/2010 04:00PM
Re: Setting group_concat_max_len 2273 Rob Reid 05/28/2010 07:18PM
Re: Setting group_concat_max_len 2088 Rick James 05/29/2010 10:33PM
Re: Setting group_concat_max_len 3086 Rob Reid 05/30/2010 04:48PM
Re: Setting group_concat_max_len 1833 Rick James 05/30/2010 06:43PM
Re: Setting group_concat_max_len 1694 Rob Reid 06/04/2010 06:27AM
Re: Setting group_concat_max_len 1530 Rick James 06/04/2010 07:10AM
Re: Setting group_concat_max_len 3100 Rob Reid 06/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.