Zware querys verbeteren

Ik zou iig zeggen om user_id, week_nr en YEAR buiten qoutes te laten aangezien dit numerieke waarden zijn.
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?

<?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;
}
?>

De kolommen:
date_hours = date
week_nr = int
Status = enum('temp', 'send', 'accept', 'denied')

Een index op user_id, week_nr.
Een index op date_hours.

Dan met GROUP BY van alle gebruikers per week de uren verzamelen:

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

Je moet altijd proberen te vermijden te filteren op functies met kolommen, dit resulteert in een full table scan.

Joni Fleischer op 22/02/2015 11:50:43

- 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.

3743 stuks geladen en opgeteld en gegroepeerd: Dat laatste, opgeteld en gegroepeerd, gebeurt dat in PHP of in MySQL?
Je kan er een efficiente query van maken die alles in 1x doet.

@ John D
In PHP

Is die effeciente query degene die @Ger net post??


[size=xsmall]Toevoeging op 22/02/2015 12:53:05:[/size]

Dit is mijn script zeg maar:

<?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>
';
}
}
?>

Die twee queries kunnen in één. Ik zou zeggen plak die query van Ger eens in phpMyAdmin onder de SQL tab en zie wat je terugkrijgt.

De kunst is dat je een resultaat krijgt waarin alleen die (maar tevens alle) gegevens staan die je nodig hebt.

Kun je niet op jaar selecteren? dus een <select> box aanmaken en dan.
<?php
$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?