[SOLVED] GROUP_CONCAT max Length?

[cooldude832]

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?

[fenway]

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.

[cooldude832]

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?

[fenway]

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.

[cooldude832]

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

[fenway]

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

[cooldude832]

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

[fenway]

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.

[cooldude832]

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