Jump to content

[SOLVED] GROUP_CONCAT max Length?


cooldude832

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?

Link to comment
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.

Link to comment
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

Link to comment
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

Link to comment
Share on other sites

This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...

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.