Setting group_concat_max_len

[Rob Reid]

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?