Jump to content

Archived

This topic is now archived and is closed to further replies.

cooldude832

[SOLVED] GROUP_CONCAT max Length?

Recommended Posts

I am using this query


Select 
languages.Name as Lang,
GROUP_CONCAT(DISTINCT(libraries.Name) ORDER BY libraries.Name) as Libs,
GROUP_CONCAT(DISTINCT(functions.Name) ORDER BY functions.Name) as Funcs,
ALib.LibraryID as Active_Lib,
ALang.Name as Active_Lang,
AFunc.FunctionID as Active_Func 

FROM `languages` 
LEFT JOIN libraries ON (libraries.LanguageID = languages.LanguageID) 
LEFT JOIN functions ON (functions.LibraryID = '2' AND functions.LanguageID = '2') 
LEFT JOIN `languages` as ALang on (ALang.LanguageID = '2')
LEFT JOIN `libraries` as ALib ON(ALib.LibraryID = '2') 
LEFT JOIN `functions` as AFunc ON(AFunc.FunctionID = '33') 

GROUP BY languages.LanguageID 

ORDER BY languages.Sort

 

Which basically builds my left navigation menu.

It has a 3 level system the upper most being the langauge then the library (so php -> mysql) and then a function (so php->mysql->myysql_query)

I'm using GROUP_CONCAT to get all those functions and libraries for the active library which works fine but because it is returning a long VARCHAR I had to up the length for the session's GROUP_CONCAT using

 

SET SESSION group_concat_max_len = 6000;

 

Is this a problem or could be a problem?

Share this post


Link to post
Share on other sites

When I need to use it, I usually use:

 

SET group_concat_max_len := @@max_allowed_packet

 

Just realize that this can be very slow if you don't know what to expect.

Share this post


Link to post
Share on other sites

At worse it will grab all the functions in a single php/mysql/xhtml/css/javascript library  so the longest is about 100 10-100 character strings which I don't think is terrible is it?

Share this post


Link to post
Share on other sites

At worse it will grab all the functions in a single php/mysql/xhtml/css/javascript library  so the longest is about 100 10-100 character strings which I don't think is terrible is it?

No, not terrible... you just have to have an idea of how many CONCAT operators will be formed, that's all.

Share this post


Link to post
Share on other sites

here is the explain.  I expected languages to have a null key but joining in the functions table I don't see how to get around it


id  	 select_type  	 table  	 type  	 possible_keys  	 key  	 key_len  	 ref  	 rows  	 Extra
1 	SIMPLE 	languages 	ALL 	NULL 	NULL 	NULL 	NULL 	4 	Using temporary; Using filesort
1 	SIMPLE 	libraries 	ref 	LanguageID 	LanguageID 	8 	db253747531.languages.LanguageID 	2 	 
1 	SIMPLE 	functions 	ALL 	Lang_Lib 	NULL 	NULL 	NULL 	46 	 
1 	SIMPLE 	ALang 	const 	PRIMARY 	PRIMARY 	8 	const 	1 	 
1 	SIMPLE 	ALib 	const 	PRIMARY 	PRIMARY 	8 	const 	1 	Using index
1 	SIMPLE 	AFunc 	const 	PRIMARY 	PRIMARY 	8 	const 	1 	Using index

 

The 46 rows in it is just about the Max it'll ever pull

 

I have an index on the 2 fields libraryid and languageid in the functions table using

Alter table `functions` ADD INDEX `Lang_Lib` ( `LanguageID` , `LibraryID` )

 

But it seems to still not pick it up right

Share this post


Link to post
Share on other sites

Why not (language_id, sort )?  And why the left joins?

Share this post


Link to post
Share on other sites

without a left join it'll return no rows if you haven't select a function or library to be in.

That option wasn't clear from the query.

Share this post


Link to post
Share on other sites

http://scriptbetter.net/

 

You can see the left navi grows/shrinks as you move and out of libraries and functions

I got the explain to have just 1 null on languages (which it should be) by changing the ON clause for functions to be

 

(functions.LibraryID = '2' AND functions.LanguageID = languages.LanguageID) 

 

I have to have a variable libraryid because that is the Join criteria, but I don't want it JOIN it in on every language's row so now the results are as I want. and I think good

 

 

results

    	   	   	   	
php 	mysql,Operators 	mysql_affected_rows,mysql_change_user,mysql_client... 	2 	php 	33
MySQL 	NULL 	NULL 	2 	php 	33
xHTML 	Tables 	NULL 	2 	php 	33
css 	NULL 	NULL 	2 	php 	33

 

EXPLAIN

1  	SIMPLE  	ALib  	const  	PRIMARY  	PRIMARY  	8  	const  	1  	Using index; Using temporary; Using filesort
1 	SIMPLE 	languages 	ALL 	NULL 	NULL 	NULL 	NULL 	4 	 
1 	SIMPLE 	libraries 	ref 	LanguageID 	LanguageID 	8 	db253747531.languages.LanguageID 	2 	 
1 	SIMPLE 	functions 	ref 	Lang_Lib,LanguageID 	Lang_Lib 	16 	db253747531.languages.LanguageID,const 	6 	 
1 	SIMPLE 	ALang 	const 	PRIMARY 	PRIMARY 	8 	const 	1 	 
1 	SIMPLE 	AFunc 	const 	PRIMARY 	PRIMARY 	8 	const 	1 	Using index

 

Query

SELECT languages.Name AS Lang, GROUP_CONCAT( DISTINCT (
libraries.Name
)
ORDER BY libraries.Name ) AS Libs, GROUP_CONCAT( DISTINCT (
functions.Name
)
ORDER BY functions.Name ) AS Funcs, ALib.LibraryID AS Active_Lib, ALang.Name AS Active_Lang, AFunc.FunctionID AS Active_Func
FROM `languages`
LEFT JOIN libraries ON ( libraries.LanguageID = languages.LanguageID )
LEFT JOIN functions ON ( functions.LibraryID = '2'
AND functions.LanguageID = languages.LanguageID )
LEFT JOIN `languages` AS ALang ON ( ALang.LanguageID = '2' )
JOIN `libraries` AS ALib ON ( ALib.LibraryID = '2' )
LEFT JOIN `functions` AS AFunc ON ( AFunc.FunctionID = '33' )
GROUP BY languages.LanguageID
ORDER BY languages.Sort

Share this post


Link to post
Share on other sites

×

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.