MySQL GROUP_CONCAT

M

Enige tijd geleden heb ik een project gedaan in Laravel, waar nogal wat “hasManyThrough” relaties in zitten. Dat was allemaal goed qua performance tijdens het bouwen, maar na enige tijd in productie kwamen er toch opmerkingen van de gebruikers over de snelheid van de website.

Daarop eens gaan zoeken wat de oorzaak is en Laravel Telescope geinstalleerd. Daarmee is heel eenvoudig te zien wat er allemaal gedaan wordt tijdens het opvragen van een website pagina. En toen schrok ik nogal van het aantal MySQL statements dat uitgevoerd wordt.

Voor een overzicht van een bepaalde table wordt er een grote model collectie opgehaald uit de database. Die collectie wordt met de dataTable.js library getoond. Maar in de view wordt deze collectie aangevuld met informatie via de hasManyThrough relatie. Gevolg was dat er voor ieder record weer een losse query uitgevoerd wordt op de database voor de hasManyThrough relatie. Zodra het aantal items in de collectie groeit, groeit het aantal opvragen uit de database dus mee met alle gevolgen van dien voor de snelheid.

Daarop ben ik afgestapt om voor de index pagina’s van de controllers alleen maar gebruik te maken van de Eloquent models, maar daar raw sql te gebruiken. Alleen kwam toen het probleem hoe ik de data die ik nodig had via de hasManyThrough relatie in 1 keer kon opvragen uit de database.

Na wat googlen kwam ik op de MySQL functie GROUP_CONCAT uit. Ik doe dit werk al een behoorlijk aantal jaar, maar die functie was echt nieuw voor mij en een mooie oplossing voor mijn probleem. Met een GROUP_CONCAT kon ik alle waarden uit de hasManyThrough relatie bij iedere regel toevoegen. Probleem opgelost en de performance is drastisch verbetert.

SELECT
    `events`.*,
	group_concat(`labels`.`name`) AS `labels`,
	COUNT(`media`.`id`) AS `media_count`
FROM `events`

LEFT JOIN `event_label` ON `events`.`id` = `event_label`.`event_id`
LEFT JOIN `labels` ON `event_label`.`label_id` = `labels`.`id`
LEFT JOIN `media` ON `events`.`id` = `media`.`event_id`
WHERE `events`.`area_id` IN ($areas)
GROUP BY `events`.`id`

Dit resulteert in een veld met daarin alle waarden uit de labels tabel, gescheiden door een komma. Dat is in de blade view weer eenvoudig te scheiden met de PHP split functie.

Mocht je hierbij tegen het probleem aan lopen dat je alle velden uit de hoofdtabel moet toevoegen in de GROUP BY, dan is ook daar een oplossing voor. Je moet dan de ONLY_FULL_GROUP_BY setting verwijderen uit de SQL_MODE. In Laravel kan dat eenvoudig met de volgende call:

\DB::statement("SET sql_mode=(SELECT REPLACE(@@sql_mode,'ONLY_FULL_GROUP_BY',''));");

De bovenstaande code is niet helemaal netjes, aangezien je eigenlijk eerst zou moeten testen of de ONLY_FULL_GROUP_BY wel gezet is. Zeker als je die daarna weer wilt aanzetten met de volgende call:

\DB::statement("SET sql_mode=(SELECT CONCAT(@@sql_mode,',ONLY_FULL_GROUP_BY',''));");

About the author

Add Comment

By Marc