[MySQL/PHP] Enorm trage query, hoe te verbeteren

[Belindo]

Mijn kennis van PHP/SQL is basic, maar altijd genoeg geweest voor wat ik wilde. Voornamelijk input naar een database, en output uit de database in een HTML tabel.

Echter loop ik nu tegen het probleem aan dat een query erg traag loopt, ik zal hieronder de situatie schetsen en de relevante code plaatsen.

Op dit moment duurt de query voor alleen vandaag (41 agents, 7 types) 16 seconden. Een maand overzicht (220 agents, 20 types) duurt 120 seconden, zonder het parsen van de highcharts grafiek.


Ik heb een MySQL table met daarin data vanuit een web formulier. Relevante velden zijn id, agent, date en type. Een voorbeeld zou zijn:

code:

id  agent   date                type
1   agent 4 11-08-2014 11:22    type 3
2   agent 3 14-08-2014 11:22    type 5
3   agent 3 13-08-2014 11:22    type 1
4   agent 3 11-08-2014 11:22    type 6
5   agent 5 13-08-2014 11:22    type 4
6   agent 6 12-08-2014 11:22    type 2
7   agent 5 12-08-2014 11:22    type 1
8   agent 6 12-08-2014 11:22    type 2

Wat ik wil, is op een pagina een HTML table weergeven met daarin de count per agent, per type. Deze HTML tabel wordt vervolgens gebruikt voor een Hhighcharts grafiek. Het addertje echter is dat de tabelheaders (type), óók uit de database moeten komen, omdat deze kunnen veranderen afhankelijk van de date-range die je wilt zien.

Uiteindelijk wil ik dus zo'n tabel als output hebben:

code:

agent   type2   type3   type5   type6
agent2  0       0       0       1
agent3  0       1       0       0
agent4  0       0       1       0
agent5  0       1       0       1
agent6  1       2       0       0

Daarom heb ik nu een aantal verschillende queries en while() loops.

- query + while loop om de type als <th> weer te geven
- query + while loop om de agents als <td> weer te geven
- vervolgens binnen de while loop van de agents wederom een query + while loop om de types te krijgen om vervolgens
- een query te doen om de count per agent per type te krijgen.

En bij dat laatste gaat het fout. Omdat ik voor een maand rapport om en nabij de 220 verschillende agents en 20 verschillende types heb, wordt de laatste query dus 4400 keer gedaan.

Op dit moment duurt de query voor alleen vandaag (41 agents, 7 types) 16 seconden. Een maand overzicht (220 agents, 20 types) duurt 120 seconden, zonder het parsen van de highcharts grafiek.

Volgens mij moet dit toch veel sneller kunnen, alleen weet ik zelf geen opties om dit anders te doen. Wel heb ik geprobeerd om de $listtypesout array te hergebruiken voor de tweede keer dat ie nodig is, alleen kreeg ik dit niet voor elkaar omdat de output steeds 'array' was. Zoeken op dit probleem is lastig, omdat ik een 'table' wil met 'dynamic headers'. Echter in combinatie met MySQL vind ik een hoop dingen waar de tables en headers slaan op MySQL en niet op HTML/PHP.

PHP:

$listagents_qry = mysqli_query($con,"SELECT agent, count(agent) as count FROM tracker WHERE date >= '$startdate 00:00:00' AND date <= '$enddate 23:59:59' GROUP BY agent ORDER BY count DESC LIMIT 10");
$listtypes_qry = mysqli_query($con,"SELECT type, count(type) as count FROM tracker WHERE date >= '$startdate 00:00:00' AND date <= '$enddate 23:59:59' GROUP BY type ORDER BY count DESC");
echo '<table style="display:none;" id="count_per_agent_tbl">';
    echo '<thead>';
        echo '<th>Agent</th>';
        while($listtypes_out=mysqli_fetch_array($listtypes_qry)){
            echo '<th>'.$listtypes_out['type'].'</th>';
        }
    echo '</thead>';
    echo '<tbody>';
        while($listagents_out=mysqli_fetch_array($listagents_qry)){
            $agent = $listagents_out['agent'];
            echo '<tr>';
                echo '<td>'.$agent.'</td>';
                $listtypes_qry = mysqli_query($con,"SELECT type, count(type) as count FROM tracker WHERE date >= '$startdate 00:00:00' AND date <= '$enddate 23:59:59' GROUP BY type ORDER BY count DESC");
                while($listtypes_out=mysqli_fetch_array($listtypes_qry)){
                    $type = $listtypes_out['type'];
                    $agent_per_type_out = mysqli_fetch_array(mysqli_query($con,"SELECT count(agent) as count FROM tracker WHERE agent = '$agent' AND type = '$type' AND date >= '$startdate 00:00:00' AND date <= '$enddate 23:59:59'"));
                    echo '<td>'.$agent_per_type_out['count'].'</td>';
                }
            echo '</tr>';
        }
    echo '</tbody>';
echo '</table>';

Ik hoop dat iemand mij kan helpen met de snelheid van deze code te verbeteren. Wellicht denk ik verkeerd en is de code goed, maar ligt het gewoon aan de snelheid van m'n database server?

Alvast bedankt

[RobIII]

Ik zit mobiel dus je code is slecht te lezen, maar vraag 1 bij dit soort zaken is altijd: hoe staan je indexen?

[Barryvdh]

wordt de laatste query dus 4400 keer gedaan

Dat lijkt me niet zo handig Je zou eens kunnen zoeken naar joins of eager loading, zodat je niet in while (en daarna nog een keer in een while) queries gaat uitvoeren.

[CrashOverDrive]

Php is enorm traag als je veel queries draait. 't is het steeds opnieuw uitvoeren wat zoveel tijd kost.
Je kan zoals Barryvdh zegt inderdaad beter met joins aan de slag gaan.

[Belindo]

RobIII, ik heb een index op id en op date. Is het handig om deze ook op type en agent te zetten?

Barryvdh, CrashOverDrive, is een join niet om een andere table erbij te betrekken? De data staat hier in één table. De joins die ik tot nu toe gebruik zijn gemaakt in een grafische omgeving als Access/MsQuery (zoals hier, dat is voor deze database helaas niet mogelijk. En ik heb te weinig inzicht/kennis om zo'n join zelf te schrijven denk ik. Waar zou ik aan moeten denken om een soortgelijk resultaat al in m'n query te krijgen?

[Rannasha]

Ik ben geen SQL guru, maar kun je niet simpelweg een enkele query gebruiken die groepeert op zowel agent als type? Bijvoorbeeld

SQL:

SELECT type, agent, COUNT(*) AS count FROM tracker GROUP BY agent, type ORDER BY agent ASC, type ASC;

Dan zou je een enkele result-set terug moeten krijgen met alle combinaties van agent, type en aantal (!= 0). Vervolgens kun je hier overheen loopen om de resultaten in een tabel te zetten, waarbij je moet opletten op gevallen waarbij de combinatie agent/type 0 keer voorkomt, die dus in de result-set niet voorkomen.

[CrashOverDrive]

Goede oplossing van Ranasha.
En als je de php kant simpel wilt houden, kan je de last ook nog bij de sql server gooien

SQL:

select *, 
(select count(*) from `agent` as `agentSub` where `agentSub`.`agent`=`agentHoofd`.`agent` and `agentSub`.`type`='type 1') as `type1_count`,
(select count(*) from `agent` as `agentSub` where `agentSub`.`agent`=`agentHoofd`.`agent` and `agentSub`.`type`='type 2') as `type2_count`
from `agent` as `agentHoofd` 
group by agent
order by agent

Waarbij je het stuk waar nu type 1 en 2 hardcoded staan. kan generen in je php met die 1e query waar je alle types mee ophaalt.
Dan kan je hele tabel in 2 queries door eerst die types op te halen (voor je kopjes) en daarna deze querie te bouwen door een regel voor iedere type te generen.

Kan je daarna in 1 keer in een simple while alles eruit kotsen
Want de data komt er nu uit in precies in rijen hoe je ze nodig hebt.

[ Voor 3% gewijzigd door CrashOverDrive op 15-08-2014 12:39 ]

[Belindo]

Rannasha, met deze gedachte heb ik gespeeld, alleen wist ik niet hoe ik dit resultaat dan in een HTML tabel kan krijgen. Maar aangezien jij dus ook met deze manier komt, ga ik hier verder wat tijd aan besteden.

Edit, goeie aanvulling van CrashOverDrive. Ik ga hier even mee stoeien.

[ Voor 15% gewijzigd door Belindo op 15-08-2014 12:40 ]

[Rannasha]

Belindo schreef op vrijdag 15 augustus 2014 @ 12:37:
Rannasha, met deze gedachte heb ik gespeeld, alleen wist ik niet hoe ik dit resultaat dan in een HTML tabel kan krijgen. Maar aangezien jij dus ook met deze manier komt, ga ik hier verder wat tijd aan besteden.

Je weet dat je X agents en Y types hebt (uit je eerdere queries). Je maakt een 2D-array van grootte X x Y, die je overal op 0 initialiseert. Je loopt over de result-set en je stopt waarde "count" op index "agent, type" van de array.

Daarna maak je een dubbele loop over de eerste en tweede index van de array om zo je HTML-tabel op te bouwen.

Het is iets meer rekenwerk aan de PHP kant, maar op die manier vervang je de 4400 losse queries die je afschiet door een enkele. Met deze aanpak kun je ook de ORDER BY weglaten in de query en dat scheelt je weer iets.

[CrashOverDrive]

Ik heb als aanvulling op mijn suggestie even een (erg cruede) voorbeeld uitgewerkt Ongetest dus don't hold it against me

PHP:

$listagents_qry = mysqli_query($con,"SELECT agent, count(agent) as count FROM tracker WHERE date >= '$startdate 00:00:00' AND date <= '$enddate 23:59:59' GROUP BY agent ORDER BY count DESC LIMIT 10");
echo '<table style="display:none;" id="count_per_agent_tbl">';
    echo '<thead>';
        echo '<th>Agent</th>';
        $q = "select *, ";
        while($listtypes_out=mysqli_fetch_array($listtypes_qry)){
            echo '<th>'.$listtypes_out['type'].'</th>';
            $q .= "(select count(*) from `agent` as `agentSub` where `agentSub`.`agent`=`agentHoofd`.`agent` and `agentSub`.`type`='".$listtypes_out['type']."') as `".$listtypes_out['type']."`,";
        }
        // laatste comma uit query verwijderen
        $q = rtrim($q, ",")
        $q .= " from `agent` as `agentHoofd`  group by agent order by agent";
        
    echo '</thead>';
    echo '<tbody>';
    
        $listQuery = mysqli_query($con, $q);
        while($listItem=mysqli_fetch_array($listQuery)){
            var_dump($listItem);
            
        }
    
    echo '</tbody>';
echo '</table>';

Het grote voordeel hiervan is dus dat php amper hoeft te denken. In die var_dump zit al alle data die je daar op de rij nodig hebt.

[ Voor 9% gewijzigd door CrashOverDrive op 15-08-2014 12:50 ]

[Gomez12]

In wezen is er met dit soort constructies (4400 query's) maar 1 probleem... Het aantal query's.

Al duurt elke query (inclusief connectie opbouw en alles) maar 1 msec, dan zit je alsnog 4,4 seconde te wachten. Duurt zo'n query 10 msec dan zit je al 44 seconden te wachten.

Met deze aantallen query's gaan allerlei dingen meetellen waar je normaliter niet (direct) rekening mee hoeft te houden (connectietijd etc etc etc) omdat die normaliter allemaal ergens tegen de nul aanliggen, maar als jij 4400x iets meer dan nul gaat doen dan gaat het toch opeens wel meetellen.

Oftewel het pad van Rannasha / CrashOverDrive gaan volgen is de beste weg,

[Verwijderd]

even een korte, maar hopelijk vanzelfsprekende aanvulling op bovenstaande tips: ga niet je HTML lopen echo-en per regel. Los van de overhead die de vele echo statements met zich mee brengt, is de leesbaarheid ongeveer 0,0, en is het gewoon bad practice.

[Belindo]

Dit specifieke probleem speelt nog wel, echter moet ik me momenteel focussen op andere projecten. Ik ben dit weekend nog wel met de suggesties van Rannasha / CrashOverDrive bezig geweest. Helaas liep de query hier ook op vast, totdat ik het proces afbrak in PhpMyAdmin.

Toen ik een test tabel van 75000 records terugbracht naar 500 records deed de oplossing het wel, en kreeg ik mooi de output van wat er in de array zat.

Helaas kan ik dit dus alsnog niet toepassen.


Ik denk dat ik maar een minder mooie oplossing maak, eerst het aantal records per agent, en wanneer je op de agent klikt dat je dan het aantal records per type voor die agent ziet.

[incaz]

Check in elk geval je queryplan even met explain.

Een betere perfomance krijg je vermoedelijk met de volgende query, omdat je geen subqueries nodig hebt (en ik weet niet hoe slim al die select counts worden wegbezuinigd.)

SQL:

SELECT
   agent, 
   SUM( if( type = 1, 1, 0)) as type1, 
   SUM( if( type = 2, 1, 0)) as type2, 
   SUM( if( type = 3, 1, 0)) as type3
FROM `agent`
GROUP BY agent

Als je 'm eerst zonder sum doet zie je hoe het werkt.

Die regels met een SUM(IF()) moet dan genereren met php, wat uiteraard erg makkelijk kan, en dan de hele query in een keer uitvoeren. Dit zou met een index op datum / agent geen enkel probleem moeten zijn, ook niet bij 75000 records,

[Belindo]

Ah, en dan weer die SUM(IF()) genereren dmv. de while loop met de Types.

Die ga ik ook even proberen. Thanks, incaz.

[Kalin848]

Je kunt het ook volledig in SQL oplossen,
zo ondervang je ook dat er een regel bij moet als je een extra type krijgt.

SQL:

SET @sql = NULL;
SELECT
  GROUP_CONCAT(DISTINCT
    CONCAT(
      'SUM(IF(type = ''',
      type,
      ''', 1, 0)) AS ',
      type
    )
  ) INTO @sql
FROM table;

SET @sql = CONCAT('SELECT agent, ', @sql, ' 
                   FROM table 
                   GROUP BY agent');

PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

[Belindo]

Bovenstaande (van Kalin848) krijg ik niet werkend. Wellicht omdat 'type' ook spaties kan bevatten. Ik heb geprobeerd om:

SQL:

CONCAT('SUM(IF(type = ''',type,''', 1, 0)) AS ',type)

aan te passen naar

SQL:

CONCAT('SUM(IF(type = ''',type,''', 1, 0)) AS ''',type,'')

zodat de Type ook tussen aanhalingstekens komt. Dan zie ik in de resultaten dat er 139 rows gevonden zijn (lijkt mij de distinct agents, echter is het eindresultaat 0.

SQL:

SET @sql = NULL;# MySQL returned an empty result set (i.e. zero rows). SELECT GROUP_CONCAT(DISTINCT CONCAT( 'SUM(IF(type = ''', type, ''', 1, 0)) AS '', type,''' ) ) INTO @sql FROM l2_esc_tracker2;# 1 row affected. SET @sql = CONCAT('SELECT agent, ', @sql, ' FROM l2_esc_tracker2 GROUP BY agent');# MySQL returned an empty result set (i.e. zero rows). PREPARE stmt FROM @sql;# MySQL returned an empty result set (i.e. zero rows). EXECUTE stmt;# Rows: 139 DEALLOCATE PREPARE stmt;# MySQL returned an empty result set (i.e. zero rows).

De andere suggesties kom ik nog op terug

[Belindo]

incaz schreef op donderdag 21 augustus 2014 @ 20:34:Een betere perfomance krijg je vermoedelijk met de volgende query, omdat je geen subqueries nodig hebt (en ik weet niet hoe slim al die select counts worden wegbezuinigd.)

SQL:

SELECT
   agent, 
   SUM( if( type = 1, 1, 0)) as type1, 
   SUM( if( type = 2, 1, 0)) as type2, 
   SUM( if( type = 3, 1, 0)) as type3
FROM `agent`
GROUP BY agent

Als je 'm eerst zonder sum doet zie je hoe het werkt.

Die regels met een SUM(IF()) moet dan genereren met php, wat uiteraard erg makkelijk kan, en dan de hele query in een keer uitvoeren. Dit zou met een index op datum / agent geen enkel probleem moeten zijn, ook niet bij 75000 records,

dit was 'm! Ik heb de Sum(if())'s met m'n While op de Types gemaakt, daarna de query uitgevoerd, en het resultaat terugkoppelen, ik had wel weer die while nodig om de <td>'s voor de Types te echoen.

Even de query gedraaid zonder datum selectie, en de pagina laadt binnen 10 seconden! Dat is op 19973 rows, en 29 unieke types en 607 unieke agents. Zee acceptabel dus. De meeste laadtijd zit 'm nog in het genereren van de HTML table en de Highcharts grafiek.

Alle anderen ook bedankt. Al waren de andere suggesties voor mij niet de oplossing (waarschijnlijk omdat ik ze gewoon niet aan de praat kreeg), ik heb er wel veel van opgestoken.

For the record, ik sprak eerder over 75000 records, dit waren er toentertijd 17500, we zitten nu bijna op de 20000.

[Kalin848]

Voor de volledigheid....

Om een tabelnaam met spaties door te kunnen geven kun je er blokhaken omheenzetten.

CONCAT('SUM(IF(type = ''',type,''', 1, 0)) AS [',type,']')

[sig69]

Kent MySQL geen pivot en unpivot?

Even de query gedraaid zonder datum selectie, en de pagina laadt binnen 10 seconden! Dat is op 19973 rows, en 29 unieke types en 607 unieke agents. Zee acceptabel dus.
...

Binnen 10 seconden, is dat 0,5 of 9,5? Nogal een verschil namelijk. 0,5 zou ik er hier nog wel doorheen krijgen, maar 9,5 absoluut niet.

[ Voor 83% gewijzigd door sig69 op 02-09-2014 10:44 ]

[Gomez12]

sig69 schreef op dinsdag 02 september 2014 @ 10:42:
[...]

Binnen 10 seconden, is dat 0,5 of 9,5? Nogal een verschil namelijk. 0,5 zou ik er hier nog wel doorheen krijgen, maar 9,5 absoluut niet.

20.000 regels in een table, highcharts grafieken. Dan gaan er veel meer dingen meetellen als een query, dan krijg je ook browserrendertijden etc erbij.

Als jij een tabel maakt 20.000 regels en je specificeert bijv nergens breedtes dan zie ik het op een gemiddelde computer al snel 4 seconden duren voordat je browser de hele tabel gerenderd heeft omdat die per regel moet gaan kijken of de breedte van de totale kolom niet aangepast moet worden.

Highcharts kost ook weer tijd etc.
Oftewel het meetpunt in dit geval is imho veels te grof om enkel maar naar de query te moeten kijken.
Wil je weten hoe lang de query duurt hang dan een timertje in php voor/na de query en dan weet je dat.

[sig69]

Ik weet niet waar je precies op reageert.. Ik snap je punt mbt de tijd voor de query, etc. Mijn punt was alleen dat de ts nu "blij" is dat het in minder dan 10 seconden draait, maar dat het (met deze kleine dataset) veel sneller moet kunnen

[Belindo]

Ik heb een timertje boven- en onderaan de pagina geplaatst om te laadtijd van de pagina te meten. Ook heb ik er eentje om de query heen gezet.

Wanneer ik een YTD query draai die ik nog niet eerder heb gedaan en dus nog niet gecached is (einddatum ergens in 2016 bijvoorbeeld):
Page: 6.64 sec
SQL: 4.26 sec

Wanneer ik dezelfde query opnieuw draai:
Page: 0.66 sec
SQL: 0.61 sec

Wellicht kan ik hier nog iets aan bijschaven?

Wat ik wel wil vermelden is dat de meeste rapporten gedraaid gaan worden met shortcuts voor 'Today', 'This Week' en 'This Month'. De kans dat iemand een custom dataselectie gaat kiezen is erg klein.

De Today (Page: 0.34, SQL: 0.20) en This Week (Page: 0.58, SQL: 0.36) queries lopen al erg snel. Het is alleen de This Month view die dus de eerste keer erg traag is.

Wanneer iemand deze query heeft opgevraagd, wordt deze dan zo gecached dat ie voor elke gebruiker al wat sneller is? Want dat zou betekenen dat de This Month view slechts 1x per maand voor 1 user iets langer duurt.

Tevens zit ik nu thuis op Wireless via VPN te kijken, dus wellicht dat dit ook weer wat tijd kost.

Edit; even een volledige query gedaan via RDP op de server zelf:
(Page: 6.41, SQL: 4.15)
Dus VPN gooit blijkbaar geen roet in het eten.

[ Voor 5% gewijzigd door Belindo op 04-09-2014 18:58 . Reden: Test op server zelf gedaan om VPN delay uit te sluiten ]

[sig69]

Een index op de juiste kolom kan een hoop verschil maken (indien nog niet aanwezig)

[Gomez12]

Belindo schreef op donderdag 04 september 2014 @ 18:57:
Ik heb een timertje boven- en onderaan de pagina geplaatst om te laadtijd van de pagina te meten. Ook heb ik er eentje om de query heen gezet.

Hoe heb je dit timertje geimplementeerd? In php of in js? Want naast de tijd die php kost heeft je browser ook nog eens tijd nodig. En als ik zie dat het verschil tussen page en sql bij lege cache al 2 seconden is dan vraag ik me af waar die 2 seconde in gaat zitten.

Wat ik wel wil vermelden is dat de meeste rapporten gedraaid gaan worden met shortcuts voor 'Today', 'This Week' en 'This Month'. De kans dat iemand een custom dataselectie gaat kiezen is erg klein.

Hangt er net vanaf hoe je dit exact gedefinieerd hebt maar veelal zijn juist deze query's zo goed als niet te cachen omdat bij elke insert in de tabel er voor vandaag een waarde bijkomt en dus de cache invalid wordt.
Dit soort dingen kunnen in een dev-/test-omgeving perfect werken omdat er daar geen inserts langskomen, maar als er elke minuut een entry binnenkomt in de live-db dan krijg je minimaal elke minuut dus de 4 seconden om je kiezen.
Daarom hanteer ik over het algemeen bij een today overzicht : alles tot het laatste half uur wat geweest is, dan blijven de resultaten in ieder geval een half uur gecached. Of ik heb ook apps gemaakt waarin ik gewoon meerdere query's deed waarin ik de query's bijv deed per uur / half uur zodat bij een dagoverzicht alleen het laatste uur / half uur niet uit de cache kwam (alleen waren die dan meer tijd-gerelateerd waardoor ik op tijd mooi de query's kon splitsen)

Wanneer iemand deze query heeft opgevraagd, wordt deze dan zo gecached dat ie voor elke gebruiker al wat sneller is? Want dat zou betekenen dat de This Month view slechts 1x per maand voor 1 user iets langer duurt.

Zonder exact het mysql caching model uit mijn hoofd te kennen vermoed ik dat die gewoon (simpel gezegd) werkt op basis van :
- Als de query gelijk blijft
- Als er geen inserts binnen het bereik van de query vielen binnen de query-periode
Dan wordt er een resultaat uit de cache getoond.
Echter als er een nieuwe entry komt voor deze maand dan is gelijk het gecachede resultaat waardeloos (want deze bevat niet de nieuwe entry) en wordt de basis query dus weer uitgevoerd.

Over het algemeen werkt het als volgt :
Als jij een query doet over 2013 dan blijft deze "eeuwig" in de query-cache staan (mits de query-cache groot genoeg is etc) want voor 2013 verandert er niets, dus de query blijft gelijk en de data-set blijft ook gelijk dus alles kan uit de cache komen.
Als jij een query doet over 2014 dan blijft deze net zolang in de query-cache staan totdat er een nieuwe entry voor 2014 is. Want dan heb je de situatie dat de query wel gelijk is, maar de data-set is niet meer gelijk dus de dataset zal opnieuw gegenereerd moeten worden

Tevens zit ik nu thuis op Wireless via VPN te kijken, dus wellicht dat dit ook weer wat tijd kost.

Edit; even een volledige query gedaan via RDP op de server zelf:
(Page: 6.41, SQL: 4.15)
Dus VPN gooit blijkbaar geen roet in het eten.

Even afhankelijk waar je page-timertje op staat (php / js), php is sowieso onafhankelijk van vpn dat is echt enkel de tijd dat de server bezig is, niet de tijd totdat de client het in beeld heeft.
Met js-timertje is de html die php verstuurt en die je browser rendert veelal niet het probleem (stel dat je 1Mb aan html produceert, hoelang duurt dit om over te brengen?) maar meer de gecombineerde tijd dat de server bezig is met het aanmaken van de html en daarna het renderen op de client vanwege "zware" libraries als highcharts etc.