Zware querys verbeteren

Overzicht Reageren

Sponsored by: Vacatures door Monsterboard

Joni Fleischer
Moderator

Joni Fleischer

22/02/2015 11:50:43
Quote Anchor link
Hallo allemaal,

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)
PHP script in nieuw venster Selecteer het PHP script
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
<?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;                                                                                                      
}

?>


Heeft iemand een betere en snellere query voor mij?
Gewijzigd op 22/02/2015 11:54:36 door Joni Fleischer
 
PHP hulp

PHP hulp

20/05/2024 15:22:21
 
Frank Nietbelangrijk

Frank Nietbelangrijk

22/02/2015 12:40:24
Quote Anchor link
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?
 
Joni Fleischer
Moderator

Joni Fleischer

22/02/2015 12:44:10
Quote Anchor link
Code (php)
PHP script in nieuw venster Selecteer het PHP script
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
<?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')
 
Ger van Steenderen
Tutorial mod

Ger van Steenderen

22/02/2015 12:45:51
Quote Anchor link
Een index op user_id, week_nr.
Een index op date_hours.

Dan met GROUP BY van alle gebruikers per week de uren verzamelen:
Code (php)
PHP script in nieuw venster Selecteer het PHP script
1
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

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
 
John D

John D

22/02/2015 12:45:52
Quote Anchor link
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.
 
Joni Fleischer
Moderator

Joni Fleischer

22/02/2015 12:48:20
Quote Anchor link
@ John D
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)
PHP script in nieuw venster Selecteer het PHP script
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
<?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>
        '
;
    }
}

?>
 
Frank Nietbelangrijk

Frank Nietbelangrijk

22/02/2015 14:39:42
Quote Anchor link
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.
 
Eeyk Vd noot

Eeyk Vd noot

23/02/2015 10:16:51
Quote Anchor link
Kun je niet op jaar selecteren? dus een <select> box aanmaken en dan.
Code (php)
PHP script in nieuw venster Selecteer het PHP script
1
2
3
<?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?
Gewijzigd op 23/02/2015 10:19:31 door Eeyk Vd noot
 



Overzicht Reageren

 
 

Om de gebruiksvriendelijkheid van onze website en diensten te optimaliseren maken wij gebruik van cookies. Deze cookies gebruiken wij voor functionaliteiten, analytische gegevens en marketing doeleinden. U vindt meer informatie in onze privacy statement.