Toon posts:

[MySQL] Derived table performance

Pagina: 1
Acties:

donderdag 15 januari 2009 12:15

Acties:
  • 0 Henk 'm!
  • cyberstalker
  • Registratie: September 2005
  • Niet online

cyberstalker

Eersteklas beunhaas

Topicstarter
Ik probeer gegevens uit een vijftal tabellen te trekken, waarbij alle tabellen eigenlijk met een full outer join zouden moeten worden gekoppeld op datum. Omdat MySQL echter geen full outer join ondersteunt heb ik bedacht te werken met een derived table die met een union alle mogelijke data geeft en daar vervolgens alle tabellen met een left join aan te koppelen.

Probleem is echter dat MySQL dan gaat werken met een temporary table en dus geen keys meer gebruikt. Dit wordt dus redelijk traag, ondanks het feit dat ik in de subquery nog niet eens alle tabellen heb neergezet (enkel als test mScoreCard).

In het kort zijn er dus tabellen voor het volgende:

Onkosten (mExpense)
Vorderingen (mDeduction)
Scores (mScoreCard)

De meeste van deze velden hebben een datum veld. Nu moeten alle mogelijke data worden weergegeven, of ze nu enkel bij vorderingen voorkomen, onkosten of scores.

De query is nogal een monster, maar heb ik er voor de volledigheid toch even bijgezet. Een explain van de query staat eronder.

SQL:
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
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168

DELIMITER //
DROP PROCEDURE IF EXISTS `get_wage_month`//
CREATE PROCEDURE `get_wage_month`
(
    personid INT,
    month INT,
    year INT
)   
READS SQL DATA
get_wage_month_body: BEGIN
    DECLARE birthday DATE;

    SELECT
        `birthdate`
    INTO
        birthday
    FROM
        `person`
    WHERE
        `pid`       =   personid;

    SELECT
        DISTINCT(`mScoreCard`.`date`)                                   AS  `work_date`,
        `mProject`.`name`                                               AS  `project`,
        get_base_wage(  `mProject`.`mPid`,
                        `mScoreCard`.`date`,
                        personid)                                       AS  `base_wage`,
        (
            SELECT
                IFNULL(SUM(`mExpense`.`amount`), 0)
            FROM
                `mExpense`
            WHERE
                `mExpense`.`fk_pid` =   personid            AND
                `mExpense`.`date`   =   `mScoreCard`.`date` AND
                `mExpense`.`unit`   =   'money'             AND
                `mExpense`.`type`   =   'wage'              AND
                `mExpense`.`status` >   0
        )
        +
        (
            SELECT
                IFNULL(SUM(`mExpense`.`amount` * `wages`.`wage`), 0)
            FROM
                `mExpense`
            JOIN
                `wages`
            ON
                years(birthday, `mExpense`.`date`)  =   `wages`.`age`   AND
                `wages`.`group_id`                  =   1
            WHERE
                `mExpense`.`fk_pid`                 =   personid            AND
                `mExpense`.`date`                   =   `mScoreCard`.`date` AND
                `mExpense`.`unit`                   =   'hours'             AND
                `mExpense`.`type`                   =   'wage'              AND
                `mExpense`.`status`                 >   0
        )
        +
        (
            SELECT
                IFNULL(SUM(`mScoreCost`.`amount`), 0)
            FROM
                `mScoreCost`
            WHERE
                `mScoreCost`.`fk_mSCid` =   `mScoreCard`.`mSCid`    AND
                `mScoreCost`.`fk_pid`   =   personid                AND
                `mScoreCost`.`unit`     =   'money'                 AND
                `mScoreCost`.`type`     =   'wage'
        )
        +
        (
            SELECT
                IFNULL(SUM(`mScoreCost`.`amount` * `wages`.`wage`), 0)
            FROM
                `mScoreCost`
            JOIN
            (
                `wages`,
                `mWageTable2project`
            )
            ON
                `wages`.`group_id`                      =   `mWageTable2project`.`fk_mWTid`
            WHERE
                years(birthday, `mScoreCard`.`date`)    =   `wages`.`age`           AND
                `mScoreCost`.`fk_mSCid`                 =   `mScoreCard`.`mSCid`    AND
                `mScoreCost`.`fk_pid`                   =   personid                AND
                `mScoreCost`.`unit`                     =   'hours'                 AND
                `mScoreCost`.`type`                     =   'wage'                  AND
                `mWageTable2project`.`fk_mPid`          =   `mScoreCard`.`fk_mPid`
        )                                                               AS  `bruto_expense`,
        (
            SELECT
                IFNULL(SUM(`mDeduction`.`amount`), 0)
            FROM
                `mDeduction`
            WHERE
                `mDeduction`.`fk_pid`                   =   personid                            AND
                `mDeduction`.`date`                     =   `mScoreCard`.`date`                 AND
                `mDeduction`.`unit`                     =   'money'                             AND
                `mDeduction`.`type`                     IN  ('bruto', 'brutovar', 'brutovast')  AND
                `mDeduction`.`status`                   >   0
        )
        +
        (
            SELECT
                IFNULL(SUM(`mDeduction`.`amount` * `wages`.`wage`), 0)
            FROM
                `mDeduction`
            JOIN
                `wages`
            ON
                years(birthday, `mDeduction`.`date`)    =   `wages`.`age`   AND
                `wages`.`group_id`                      =   1
            WHERE
                `mDeduction`.`fk_pid`                   =   personid                            AND
                `mDeduction`.`date`                     =   `mScoreCard`.`date`                 AND
                `mDeduction`.`unit`                     =   'hours'                             AND
                `mDeduction`.`type`                     IN  ('bruto', 'brutovar', 'brutovast')  AND
                `mDeduction`.`status`                   >   0
        )                                                               AS  `bruto_deduction`,
        (
            SELECT
                IFNULL(SUM(`mExpense`.`amount`), 0)
            FROM
                `mExpense`
            WHERE
                `mExpense`.`fk_pid`     =   personid            AND
                `mExpense`.`date`       =   `mScoreCard`.`date` AND
                `mExpense`.`type`       =   'expense'           AND
                `mExpense`.`status`     >   0
        )                                                               AS  `netto_expense`
    FROM
    (
        SELECT
            `mScoreCard`.`date`
        FROM
            `mScoreCard`
        JOIN
            `mScoreEntry`
        ON
            `mScoreCard`.`mSCid`                        =   `mScoreEntry`.`fk_mSCid`
        WHERE
            DATE_FORMAT(`mScoreCard`.`date`, '%Y')      =   year                        AND
            DATE_FORMAT(`mScoreCard`.`date`, '%m')      =   month                       AND
            `mScoreCard`.`status`                       >   0                           AND
            `mScoreEntry`.`fk_pid`                      =   personid
    )                                                                   AS  `event_dates`
    LEFT JOIN
        `mScoreCard`                                                    AS  `mScoreCard`
    ON
        `event_dates`.`date`                                            =   `mScoreCard`.`date`
    JOIN
        `mProject`                                                      AS  `mProject`
    ON  
        `mScoreCard`.`fk_mPid`                                          =   `mProject`.`mPid`
    JOIN
        `mScoreEntry`                                                   AS  `mScoreEntry`
    ON 
        `mScoreCard`.`mSCid`                                            =   `mScoreEntry`.`fk_mSCid`
    WHERE
        `mScoreEntry`.`fk_pid`                                          =   personid                AND
        DATE_FORMAT(`mScoreCard`.`date`, '%Y')                          =   year                    AND
        DATE_FORMAT(`mScoreCard`.`date`, '%m')                          =   month                   AND
        `mScoreCard`.`status`                                           >   0
    GROUP BY
        `mScoreCard`.`mSCid`;
END//
DELIMITER ;


code:
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22

+----+--------------------+--------------------+--------+------------------+----------+---------+-------------------------------+------+---------------------------------+
| id | select_type        | table              | type   | possible_keys    | key      | key_len | ref                           | rows | Extra                           |
+----+--------------------+--------------------+--------+------------------+----------+---------+-------------------------------+------+---------------------------------+
|  1 | PRIMARY            | <derived9>         | ALL    | NULL             | NULL     | NULL    | NULL                          |   58 | Using temporary; Using filesort | 
|  1 | PRIMARY            | mScoreEntry        | ref    | fk_mSCid,fk_pid  | fk_pid   | 4       | const                         |   10 |                                 | 
|  1 | PRIMARY            | mScoreCard         | eq_ref | PRIMARY,fk_mPid  | PRIMARY  | 4       | sqpeople.mScoreEntry.fk_mSCid |    1 |                                 | 
|  1 | PRIMARY            | mProject           | eq_ref | PRIMARY          | PRIMARY  | 4       | sqpeople.mScoreCard.fk_mPid   |    1 |                                 | 
|  9 | DERIVED            | mScoreEntry        | ref    | fk_mSCid,fk_pid  | fk_pid   | 4       |                               | 1416 |                                 | 
|  9 | DERIVED            | mScoreCard         | eq_ref | PRIMARY          | PRIMARY  | 4       | sqpeople.mScoreEntry.fk_mSCid |    1 | Using where                     | 
|  8 | DEPENDENT SUBQUERY | mExpense           | ref    | fk_pid           | fk_pid   | 4       | const                         |   34 | Using where                     | 
|  7 | DEPENDENT SUBQUERY | mDeduction         | ref    | fk_pid           | fk_pid   | 4       | const                         |   62 | Using where                     | 
|  7 | DEPENDENT SUBQUERY | wages              | ref    | group_id,age     | age      | 1       | func                          |    6 | Using where                     | 
|  6 | DEPENDENT SUBQUERY | mDeduction         | ref    | fk_pid           | fk_pid   | 4       | const                         |   62 | Using where                     | 
|  5 | DEPENDENT SUBQUERY | mWageTable2project | ref    | fk_mWTid,fk_mPid | fk_mPid  | 4       | sqpeople.mScoreCard.fk_mPid   |    1 |                                 | 
|  5 | DEPENDENT SUBQUERY | mScoreCost         | ref    | fk_mSCid,fk_pid  | fk_mSCid | 4       | sqpeople.mScoreCard.mSCid     |    4 | Using where                     | 
|  5 | DEPENDENT SUBQUERY | wages              | ref    | group_id,age     | age      | 1       | func                          |    6 | Using where                     | 
|  4 | DEPENDENT SUBQUERY | mScoreCost         | ref    | fk_mSCid,fk_pid  | fk_mSCid | 4       | sqpeople.mScoreCard.mSCid     |    4 | Using where                     | 
|  3 | DEPENDENT SUBQUERY | mExpense           | ref    | fk_pid           | fk_pid   | 4       | const                         |   34 | Using where                     | 
|  3 | DEPENDENT SUBQUERY | wages              | ref    | group_id,age     | age      | 1       | func                          |    6 | Using where                     | 
|  2 | DEPENDENT SUBQUERY | mExpense           | ref    | fk_pid           | fk_pid   | 4       | const                         |   34 | Using where                     | 
+----+--------------------+--------------------+--------+------------------+----------+---------+-------------------------------+------+---------------------------------+
17 rows in set (0.03 sec)

Ik ontken het bestaan van IE.

donderdag 15 januari 2009 15:22

Acties:
  • 0 Henk 'm!
  • pedorus
  • Registratie: Januari 2008
  • Niet online

pedorus

Die uitlijning van de query maakt het er niet overzichtelijker op... :)
Ik zou voor iedere subquery een view maken (met gebruik group by op personid). Dan is het nog een kwestie van die views (left-)joinen en de resultaten optellen als ik het goed zie. In plaats van date-format lijkt me year() en month() een stuk makkelijker.

Vitamine D tekorten in Nederland | Dodelijk coronaforum gesloten

dinsdag 20 januari 2009 18:43

Acties:
  • 0 Henk 'm!
  • cyberstalker
  • Registratie: September 2005
  • Niet online

cyberstalker

Eersteklas beunhaas

Topicstarter
Dankjewel, die tip van year() en month() is goed.

Het maken van verschillende views lost niets op, want die views gebruiken dan sowieso allemaal algorithm = temptable.

De oplossing in mijn geval was om SELECT STRAIGHT_JOIN te gebruiken.

Ik ontken het bestaan van IE.

Reageer

Apple iPhone 16e LG OLED evo G5 Google Pixel 10 Samsung Galaxy S25 Star Wars: Outlaws Nintendo Switch 2 Apple AirPods Pro (2e generatie) Sony PlayStation 5 Pro

Tweakers is onderdeel van DPG Media B.V.
Alle rechten voorbehouden - Auteursrecht © 1998 - 2025 • Hosting door TrueFullstaq