Query opsplitsen in tweeen
Ik heb een query die werkt, maar erg traaaag is.
Het lijkt me beter om hem in tweeen te splitsen, maar ik weet niet precies hoe...
Dit is de query:
mysql_query("SELECT YEAR(mct_sessions.date) AS Year, MONTH(mct_sessions.date) AS Month, COUNT(mct_paths.id) AS Pageviews, COUNT(DISTINCT mct_sessions.visitors_id) AS Visitors FROM mct_sessions INNER JOIN mct_paths ON mct_paths.session_id = mct_sessions.id WHERE mct_sessions.date BETWEEN date_format( str_to_date( '$startDates', '%e-%c-%Y' ) , '%Y-%m-%d' ) AND date_format( str_to_date( '$endDates', '%e-%c-%Y' ) , '%Y-%m-%d' ) GROUP BY Year, Month"
Het is een INNER JOIN van mct_paths en mct_sessions.
Hoe is het bijvoorbeeld gesteld met de normalisatie van je datamodel en hoeveel records praten we hier over. Heb je verder op een slimme manier indexen aangebracht op de kolommen in je tabel?
Dat laatste is voornamelijk in een tabel met veel records van groot belang. Gister nog kwam er iemand achter dat een query die eerst 40 seconde duurde, na het aanbrengen van indexen op de juiste kolommen nog maar 1 seconde duurde!
Code (php)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
SELECT
YEAR(mct_sessions.date) AS Year,
MONTH(mct_sessions.date) AS Month,
COUNT(mct_paths.id) AS Pageviews,
COUNT(DISTINCT mct_sessions.visitors_id) AS Visitors
FROM
mct_sessions INNER JOIN
mct_paths ON mct_paths.session_id = mct_sessions.id
WHERE
mct_sessions.date
BETWEEN
date_format( str_to_date( '$startDates', '%e-%c-%Y' ) , '%Y-%m-%d' )
AND
date_format( str_to_date( '$endDates', '%e-%c-%Y' ) , '%Y-%m-%d' )
GROUP BY
Year, Month
YEAR(mct_sessions.date) AS Year,
MONTH(mct_sessions.date) AS Month,
COUNT(mct_paths.id) AS Pageviews,
COUNT(DISTINCT mct_sessions.visitors_id) AS Visitors
FROM
mct_sessions INNER JOIN
mct_paths ON mct_paths.session_id = mct_sessions.id
WHERE
mct_sessions.date
BETWEEN
date_format( str_to_date( '$startDates', '%e-%c-%Y' ) , '%Y-%m-%d' )
AND
date_format( str_to_date( '$endDates', '%e-%c-%Y' ) , '%Y-%m-%d' )
GROUP BY
Year, Month
Vervolgens ga je met EXPLAIN uitvogelen waaróm de query langzaam is. Lukraak de boel opknippen gaat je daar namelijk niet bij helpen.
De functies date_format() en str_to_date zullen in elk geval niet bijdragen aan het versnellen van het process, integendeel! Waarom geef je niet gewoon een correcte dataum op als input voor de query?
Gewijzigd op 01/01/1970 01:00:00 door Frank -
Ik heb volgens mij wel ver door genormaliseerd, ieerst was het minder ver door genormaliseerd en toen leek het juist sneller te gaan. Ik dacht misschien ligt het aan de JOINS die het traag maken.
Het gaat nu nog maar over circa 15000 records, maar dat zullen er alleen maar meer worden.
Dat indexeren dat heb ik nog niet geprobeerd, dus dat ga ik eens proberen.
pgFrank schreef op 28.01.2008 14:40:
De functies date_format() en str_to_date zullen in elk geval niet bijdragen aan het versnellen van het process, integendeel! Waarom geef je niet gewoon een correcte dataum op als input voor de query?
Ja, dat komt omdat de ingevoerde datum's gewoon op de 'europese' manier zijn ingevoerd. Ik kan het dan inderdaad beter met php eerst omzetten naar de goeie format...
MySQL Indexes
Zie deze tutorial en ga met EXPLAIN aan de gang om uit te vogelen waarom jouw query traag is.
Gewijzigd op 01/01/1970 01:00:00 door Joren de Wit
bedankt voor de snelle en goede reacties..
Pelckie schreef op 28.01.2008 14:48:
Ja, dat komt omdat de ingevoerde datum's gewoon op de 'europese' manier zijn ingevoerd. Ik kan het dan inderdaad beter met php eerst omzetten naar de goeie format...
ISO-formaat is ook Europees hoor.pgFrank schreef op 28.01.2008 14:40:
De functies date_format() en str_to_date zullen in elk geval niet bijdragen aan het versnellen van het process, integendeel! Waarom geef je niet gewoon een correcte dataum op als input voor de query?
Ja, dat komt omdat de ingevoerde datum's gewoon op de 'europese' manier zijn ingevoerd. Ik kan het dan inderdaad beter met php eerst omzetten naar de goeie format...
Daarnaast zul je toch al in PHP moeten controleren of de datum wel een geldige datum is, MySQL zal dat meestal niet doen. Wanneer de datum correct is, zet hem in de juiste volgorde en slinger hem de query in. Scheelt weer performance aan de database-zijde.
ps. Welke versie van MySQL gebruik je? En welke MODE gebruik je?
Gewijzigd op 01/01/1970 01:00:00 door Jurgen assaasas
Met versie 5 van MySQL wordt het al beter, maar ook dan laat deze database nog genoeg te wensen over.
Ik weet niet bij welke host je zit, maar vraag dus eens hoe het zit met update van MySQL. Zijn ze niet van plan om hier in de zeer nabije toekomst verandering in te bregen, zou ik toch ten zeerste overwegen om over te stappen naar een andere host.
ps. Welke PHP versie draaien ze? Als dat op dit moment geen versie 5 is, heb je nog een reden om over te stappen ;-)
Ik ga in ieder geval mijn best doen om ze ervan te overtuigen dat het zo niet langer kan.
Is het trouwens omslachtig om een bestaand systeem om te bouwen van myISAM naar INNODB?
Pelckie schreef op 29.01.2008 10:27:
Bepaar je de moeite en zoek een andere host. Dat levert je veel meer op dan een stugge host van zijn fouten proberen te overtuigen. PHP 4 en MySQL 4, het is een schande!Ik ga in ieder geval mijn best doen om ze ervan te overtuigen dat het zo niet langer kan.
Verhuizen van host zal je veel minder problemen geven ;-)
Om over te stappen op InnoDB zul je je tabellen opnieuw aan moeten maken. Als je echter al een goed datamodel hebt, hoef vervolgens alleen nog de onderlinge relaties tussen de tabellen aan te brengen dmv foreign keys. Tenslotte kun je de data gewoon kopieren vanuit de oude tabellen naar de nieuwe tabellen.
Het is dan alleen te hopen dat er nog geen conflicten zijn met de constraints die je aangebracht hebt. Is dat wel het geval, dan zal het waarschijnlijk het makkelijkste zijn om de tabellen weer helemaal opnieuw te vullen.
De crux zit hem nu nog in een klein stukje code:
Code (php)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
<?php
while($Session = mysql_fetch_assoc($Query_All))//start while loop through paths
{
$end=0;
$y=0;
$Query_Session = MySQL_Query("SELECT page_id FROM mct_paths WHERE session_id ='".$Session['session_id']."' ");
while($Page_ID = mysql_fetch_assoc($Query_Session)) //&& $End == 0) //start while in while
{
$y++;
$Query_Pages = MySQL_Query("SELECT page_title FROM mct_hitpages WHERE id='".$Page_ID[page_id]."'");
$Page = mysql_fetch_assoc($Query_Pages);
$PageTotal = mysql_num_rows($Query_Session);
$arrow = ($y == $PageTotal) ? "" : "<img src='images/arrow.png'>";
if(strlen($List[$i])<350)
{
$List[$i] .= $Page['page_title'].$arrow;//fill array
}
else
{
if ($end==0)
{
$List[$i] .= "...";//fill array
$end=1;
}
}
}
$i++;
}
?>
while($Session = mysql_fetch_assoc($Query_All))//start while loop through paths
{
$end=0;
$y=0;
$Query_Session = MySQL_Query("SELECT page_id FROM mct_paths WHERE session_id ='".$Session['session_id']."' ");
while($Page_ID = mysql_fetch_assoc($Query_Session)) //&& $End == 0) //start while in while
{
$y++;
$Query_Pages = MySQL_Query("SELECT page_title FROM mct_hitpages WHERE id='".$Page_ID[page_id]."'");
$Page = mysql_fetch_assoc($Query_Pages);
$PageTotal = mysql_num_rows($Query_Session);
$arrow = ($y == $PageTotal) ? "" : "<img src='images/arrow.png'>";
if(strlen($List[$i])<350)
{
$List[$i] .= $Page['page_title'].$arrow;//fill array
}
else
{
if ($end==0)
{
$List[$i] .= "...";//fill array
$end=1;
}
}
}
$i++;
}
?>
Het moet nu telkens opnieuw een query uitvoeren, heeft iemand een idee hoe dit op een slimmere manier aan te pakken?
Gewijzigd op 01/01/1970 01:00:00 door Pelckie
Binnen een while-lus een query uitvoeren is zelden een goed plan, dat kan een enorme berg queries opleveren.
Ps. $Page_ID[page_id] mag worden $Page_ID['page_id'] dus met quotes. page_id is geen constante maar een string. Hier had je ook een notice op horen te krijgen. Of heb je de error_reporting op een te laag niveau staan?
Laat anders eens zien hoe je datamodel eruit ziet en omschrijf eens wat je precies uit die tabellen wilt ophalen. Dan kunnen we eens kijken :-)
pgFrank schreef op 29.01.2008 17:19:
Ps. $Page_ID[page_id] mag worden $Page_ID['page_id'] dus met quotes. page_id is geen constante maar een string. Hier had je ook een notice op horen te krijgen. Of heb je de error_reporting op een te laag niveau staan?
Bedankt, ik weet dat dit tussen quotes moet, was een typfout. Error report staat idd op een laag niveau, sterker nog het staat uit. Dus als ik bijvoorbeeld syntax errors heb dan is dat wel is lastig...
Gewijzigd op 01/01/1970 01:00:00 door Pelckie
Pelckie schreef op 29.01.2008 17:24:
Doe je ook wat met de anderen opmerkingen? Die zijn namelijk veel waardevoller...Bedankt, ik weet dat dit tussen quotes moet, was een typfout.
Blanche schreef op 29.01.2008 17:21:
Laat anders eens zien hoe je datamodel eruit ziet en omschrijf eens wat je precies uit die tabellen wilt ophalen. Dan kunnen we eens kijken :-)
Sorry dat gaat me vandaag niet meer lukken. Ik zal morgen het datamodel even posten.
@ Blanche -> Ja daar doe ik zeker wat mee... niet zo snel... haha
Bedankt weer voor de snelle reacties!
Gewijzigd op 01/01/1970 01:00:00 door Pelckie