Zware querys verbeteren
Ik heb een uren registratie programma geschreven. Nu werkte het allemaal zeer goed, tot er 2 jaar aan uren in verwerkt zijn. Nu als je als ADMIN de uren wilt beheren, dan is de laadtijd aanzienlijk zeg maar.
Ik denk dat het komt door het volgende:
- Ten eerste worden ALLE uren geladen in 1 keer. (3743 stuks)
- Daarna wordt er per record gekeken of ze gegroepeerd kunnen worden
- daarna wordt van elke record gekeken hoeveel uur ze hebben gemaakt totaal in een week
-- Deze worden opgeteld en gegroepeerd.
Dat is deze 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
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
<?php
$duedt = explode("-",$row['date_hours']);
$date = mktime(0, 0, 0, $duedt[1], $duedt[2],$duedt[0]);
$sql = "
SELECT
hours
FROM
hours
WHERE
user_id = '".$row['user_id']."'
AND
status = 'accept'
AND
week_nr = '".$row['week_nr']."'
AND
YEAR(date_hours) = '".$duedt[0]."'
AND
archive = 'no'
";
if($res2 = mysqli_query($mysqli, $sql))
{
$aantal_uur = array();
while($hours = mysqli_fetch_assoc($res2)):
$aantal_uur[] = ($hours['hours'] >= 0) ? $hours['hours'] : $hours['hours'] + 24;
endwhile;
}
?>
$duedt = explode("-",$row['date_hours']);
$date = mktime(0, 0, 0, $duedt[1], $duedt[2],$duedt[0]);
$sql = "
SELECT
hours
FROM
hours
WHERE
user_id = '".$row['user_id']."'
AND
status = 'accept'
AND
week_nr = '".$row['week_nr']."'
AND
YEAR(date_hours) = '".$duedt[0]."'
AND
archive = 'no'
";
if($res2 = mysqli_query($mysqli, $sql))
{
$aantal_uur = array();
while($hours = mysqli_fetch_assoc($res2)):
$aantal_uur[] = ($hours['hours'] >= 0) ? $hours['hours'] : $hours['hours'] + 24;
endwhile;
}
?>
Heeft iemand een betere en snellere query voor mij?
Gewijzigd op 22/02/2015 11:54:36 door Joni Fleischer
Daarnaast zou je de kolommen kunnen indexeren die in je WHERE gedeelte staan.
Ik ben tevens benieuwd welke typen de kolommen uit de hours tabel hebben. (bijv. integer, varchar etc)
Daarnaast: voor de kolom 'status': Hoeveel verschillende waardes kent deze kolom?
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
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
<?php
$duedt = explode("-",$row['date_hours']);
$date = mktime(0, 0, 0, $duedt[1], $duedt[2],$duedt[0]);
$sql = "
SELECT
hours
FROM
hours
WHERE
user_id = $row['user_id']
AND
status = 'accept'
AND
week_nr = $row['week_nr']
AND
YEAR(date_hours) = $duedt[0]
AND
archive = 'no'
";
if($res2 = mysqli_query($mysqli, $sql))
{
$aantal_uur = array();
while($hours = mysqli_fetch_assoc($res2)):
$aantal_uur[] = ($hours['hours'] >= 0) ? $hours['hours'] : $hours['hours'] + 24;
endwhile;
}
?>
$duedt = explode("-",$row['date_hours']);
$date = mktime(0, 0, 0, $duedt[1], $duedt[2],$duedt[0]);
$sql = "
SELECT
hours
FROM
hours
WHERE
user_id = $row['user_id']
AND
status = 'accept'
AND
week_nr = $row['week_nr']
AND
YEAR(date_hours) = $duedt[0]
AND
archive = 'no'
";
if($res2 = mysqli_query($mysqli, $sql))
{
$aantal_uur = array();
while($hours = mysqli_fetch_assoc($res2)):
$aantal_uur[] = ($hours['hours'] >= 0) ? $hours['hours'] : $hours['hours'] + 24;
endwhile;
}
?>
De kolommen:
date_hours = date
week_nr = int
Status = enum('temp', 'send', 'accept', 'denied')
Een index op date_hours.
Dan met GROUP BY van alle gebruikers per week de uren verzamelen:
Code (php)
1
2
3
4
5
6
7
8
9
10
11
12
13
2
3
4
5
6
7
8
9
10
11
12
13
SELECT
user_id,
week_nr,
SUM(hours) total_hours
FROM
hours
WHERE
status = 'accept'
AND
archive = 'no'
AND
date_hours BETWEEN STR_TO_DATE(CONCAT YEAR(CURRENT_DATE), '-01-01'), '%Y-%m-%d') AND CURRENT_DATE
GROUP_BY user_id, week_nr
user_id,
week_nr,
SUM(hours) total_hours
FROM
hours
WHERE
status = 'accept'
AND
archive = 'no'
AND
date_hours BETWEEN STR_TO_DATE(CONCAT YEAR(CURRENT_DATE), '-01-01'), '%Y-%m-%d') AND CURRENT_DATE
GROUP_BY user_id, week_nr
Je moet altijd proberen te vermijden te filteren op functies met kolommen, dit resulteert in een full table scan.
Gewijzigd op 22/02/2015 12:51:18 door Ger van Steenderen
Joni Fleischer op 22/02/2015 11:50:43:
3743 stuks geladen en opgeteld en gegroepeerd: Dat laatste, opgeteld en gegroepeerd, gebeurt dat in PHP of in MySQL?- Ten eerste worden ALLE uren geladen in 1 keer. (3743 stuks)
- Daarna wordt er per record gekeken of ze gegroepeerd kunnen worden
- daarna wordt van elke record gekeken hoeveel uur ze hebben gemaakt totaal in een week
- Deze worden opgeteld en gegroepeerd.
- Daarna wordt er per record gekeken of ze gegroepeerd kunnen worden
- daarna wordt van elke record gekeken hoeveel uur ze hebben gemaakt totaal in een week
- Deze worden opgeteld en gegroepeerd.
Je kan er een efficiente query van maken die alles in 1x doet.
In PHP
Is die effeciente query degene die @Ger net post??
Toevoeging op 22/02/2015 12:53:05:
Dit is mijn script zeg maar:
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
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
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
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
<?php
$sql = "
SELECT
*
FROM
hours
WHERE
status = 'accept'
AND
archive = 'no'
GROUP BY
user_id, week_nr, YEAR(date_hours)
";
if(!$res = mysqli_query($mysqli, $sql))
{
trigger_error('Fout in query: '.mysqli_error());
}
else
{
while($row = mysqli_fetch_assoc($res))
{
$duedt = explode("-",$row['date_hours']);
$date = mktime(0, 0, 0, $duedt[1], $duedt[2],$duedt[0]);
$sql = "
SELECT
hours
FROM
hours
WHERE
user_id = ".$row['user_id']."
AND
status = 'accept'
AND
week_nr = ".$row['week_nr']."
AND
YEAR(date_hours) = ".$duedt[0]."
AND
archive = 'no'
";
if($res2 = mysqli_query($mysqli, $sql))
{
$aantal_uur = array();
while($hours = mysqli_fetch_assoc($res2)):
$aantal_uur[] = ($hours['hours'] >= 0) ? $hours['hours'] : $hours['hours'] + 24;
endwhile;
}
$u_meta = "SELECT * FROM wp_usermeta WHERE user_id = '".$row['user_id']."'";
if($result_meta = $mysqli->query($u_meta)):
while($row_meta = mysqli_fetch_assoc($result_meta)):
if($row_meta['meta_key'] == "first_name"): $voornaam = $row_meta['meta_value']; endif;
if($row_meta['meta_key'] == "last_name"): $achternaam = $row_meta['meta_value']; endif;
endwhile;
endif;
echo '
<tr>
<td>'.$voornaam.' '.$achternaam.'</td>
<td class="center">'.$row['week_nr'].'</td>
<td>'.date('Y', $date).'</td>
<td class="center">'.array_sum($aantal_uur).'</td>
<td class="center">
<a class="btn btn-success btn-xs"
href="'.SITE_URL.'admin/details_uren_bekijken.php?id='.$row['user_id'].'&week_nr='.$row['week_nr'].'&jaar='.$duedt[0].'">
<span class="glyphicon glyphicon-eye-open"></span>
Bekijken
</a>
</td>
</tr>
';
}
}
?>
$sql = "
SELECT
*
FROM
hours
WHERE
status = 'accept'
AND
archive = 'no'
GROUP BY
user_id, week_nr, YEAR(date_hours)
";
if(!$res = mysqli_query($mysqli, $sql))
{
trigger_error('Fout in query: '.mysqli_error());
}
else
{
while($row = mysqli_fetch_assoc($res))
{
$duedt = explode("-",$row['date_hours']);
$date = mktime(0, 0, 0, $duedt[1], $duedt[2],$duedt[0]);
$sql = "
SELECT
hours
FROM
hours
WHERE
user_id = ".$row['user_id']."
AND
status = 'accept'
AND
week_nr = ".$row['week_nr']."
AND
YEAR(date_hours) = ".$duedt[0]."
AND
archive = 'no'
";
if($res2 = mysqli_query($mysqli, $sql))
{
$aantal_uur = array();
while($hours = mysqli_fetch_assoc($res2)):
$aantal_uur[] = ($hours['hours'] >= 0) ? $hours['hours'] : $hours['hours'] + 24;
endwhile;
}
$u_meta = "SELECT * FROM wp_usermeta WHERE user_id = '".$row['user_id']."'";
if($result_meta = $mysqli->query($u_meta)):
while($row_meta = mysqli_fetch_assoc($result_meta)):
if($row_meta['meta_key'] == "first_name"): $voornaam = $row_meta['meta_value']; endif;
if($row_meta['meta_key'] == "last_name"): $achternaam = $row_meta['meta_value']; endif;
endwhile;
endif;
echo '
<tr>
<td>'.$voornaam.' '.$achternaam.'</td>
<td class="center">'.$row['week_nr'].'</td>
<td>'.date('Y', $date).'</td>
<td class="center">'.array_sum($aantal_uur).'</td>
<td class="center">
<a class="btn btn-success btn-xs"
href="'.SITE_URL.'admin/details_uren_bekijken.php?id='.$row['user_id'].'&week_nr='.$row['week_nr'].'&jaar='.$duedt[0].'">
<span class="glyphicon glyphicon-eye-open"></span>
Bekijken
</a>
</td>
</tr>
';
}
}
?>
De kunst is dat je een resultaat krijgt waarin alleen die (maar tevens alle) gegevens staan die je nodig hebt.
Code (php)
1
2
3
2
3
<?php
$u_meta = "SELECT * FROM wp_usermeta WHERE user_id = '".$row['user_id']."' AND YEAR = '".$_POST['jaartal']."'";
?>
$u_meta = "SELECT * FROM wp_usermeta WHERE user_id = '".$row['user_id']."' AND YEAR = '".$_POST['jaartal']."'";
?>
dan hoef je ook niet data uit 2013 te laden, dat lijkt me niet nodig te zijn?
Gewijzigd op 23/02/2015 10:19:31 door Eeyk Vd noot