FOK!forum / Digital Corner / [MySQL] Inefficiente view
cyberstalkerwoensdag 28 maart 2007 @ 11:51
Ik heb een probleem met een view die in mysql vreselijk inefficient werkt. Het vreemde is dat wanneer ik de query zelf invoer i.p.v. de view te gebruiken, het geheel wel snel werkt.

De view:
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		CREATE VIEW
    person_info
(
    picture,
    personid,
    name_first,
    name_suffix,
    name_last,
    birthday,
    age,
    gsm,
    license,
    status,
    work_count
)
AS SELECT
    mFaces.filename                     AS        picture,
    person.pid                          AS        personid,
    person.firstname                    AS        name_first,
    person.suffix                       AS        name_suffix,
    person.lastname                     AS        name_last,
    person.birthdate                    AS        birthday,
    age(person.birthdate)               AS        age,
    person.phoneMob                     AS        gsm,
    person.license                      AS        license,
    peopleStatusOptions.name            AS        status,
    COUNT(DISTINCT(mScoreCard.mSCid))   AS        work_count
FROM
    person
JOIN
    peopleStatusOptions
ON
    person.status                       =         peopleStatusOptions.psId
LEFT JOIN
(
    mScoreCard,
    mScoreEntry
)
ON
    mScoreCard.replacedBy               =         -1                          AND
    mScoreCard.mSCid                    =         mScoreEntry.fk_mSCid        AND
    mScoreEntry.fk_pid                  =         person.pid
LEFT JOIN
    mFaces
ON
    person.pid                          =         mFaces.fk_pid
GROUP BY
    person.pid

Deze view roep ik dan vervolgens aan als
1SELECT * FROM person_info where personid = 851;

Dit werkt, maar is vreselijk traag. Wanneer ik nu als query het volgende opgeef:
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		SELECT
    mFaces.filename                     AS        picture,
    person.pid                          AS        personid,
    person.firstname                    AS        name_first,
    person.suffix                       AS        name_suffix,
    person.lastname                     AS        name_last,
    person.birthdate                    AS        birthday,
    age(person.birthdate)               AS        age,
    person.phoneMob                     AS        gsm,
    person.license                      AS        license,
    peopleStatusOptions.name            AS        status,
    COUNT(DISTINCT(mScoreCard.mSCid))   AS        work_count
FROM
    person
JOIN
    peopleStatusOptions
ON
    person.status                       =         peopleStatusOptions.psId
LEFT JOIN
(
    mScoreCard,
    mScoreEntry
)
ON
    mScoreCard.replacedBy               =         -1                          AND
    mScoreCard.mSCid                    =         mScoreEntry.fk_mSCid        AND
    mScoreEntry.fk_pid                  =         person.pid
LEFT JOIN
    mFaces
ON
    person.pid                          =         mFaces.fk_pid
WHERE
    person.pid                          =         851
GROUP BY
    person.pid

krijg ik hetzelfde resultaat, alleen dan een heel stuk sneller. Nu wil ik, vanwege het grote aantal tabellen in de db, graag met views werken. Op deze manier heeft dat echter weinig zin. Hoe krijg ik de view op snelheid?
JeRawoensdag 28 maart 2007 @ 14:42
Een EXPLAIN would be nice.
cyberstalkerwoensdag 28 maart 2007 @ 15:26
1
2
3
4
5
6
7
8
9
10
11
12		EXPLAIN EXTENDED SELECT * FROM person_info WHERE personid = 851;
+----+-------------+---------------------+--------+---------------+---------+---------+------------------------------+-------+---------------------------------+
| id | select_type | table               | type   | possible_keys | key     | key_len | ref                          | rows  | Extra                           |
+----+-------------+---------------------+--------+---------------+---------+---------+------------------------------+-------+---------------------------------+
|  1 | PRIMARY     | <derived2>          | ALL    | NULL          | NULL    | NULL    | NULL                         |  1301 | Using where                     |
|  2 | DERIVED     | person              | ALL    | NULL          | NULL    | NULL    | NULL                         |  1306 | Using temporary; Using filesort |
|  2 | DERIVED     | peopleStatusOptions | eq_ref | PRIMARY       | PRIMARY | 1       | tsu_dev.person.status        |     1 |                                 |
|  2 | DERIVED     | mScoreEntry         | ALL    | NULL          | NULL    | NULL    | NULL                         | 53143 |                                 |
|  2 | DERIVED     | mScoreCard          | eq_ref | PRIMARY       | PRIMARY | 4       | tsu_dev.mScoreEntry.fk_mSCid |     1 |                                 |
|  2 | DERIVED     | mFaces              | eq_ref | fk_pid        | fk_pid  | 4       | tsu_dev.person.pid           |     1 |                                 |
+----+-------------+---------------------+--------+---------------+---------+---------+------------------------------+-------+---------------------------------+
6 rows in set, 1 warning (1 min 52.91 sec)


De herschreven query toont niets bijzonders.
JeRawoensdag 28 maart 2007 @ 15:35
Die mScoreEntry gebruikt geen indices zo te zien; en indien mogelijk moet je voorkomen dat er een filesort plaatsvindt adhv. de tabel person
cyberstalkerwoensdag 28 maart 2007 @ 15:48
Het probleem ligt niet bij de indices. De gewone query draait prima snel. Ik denk dat ik nu weet wat het probleem is, echter nog niet hoe ik het kan oplossen. MySQL maakt een temporary table aan, en is een hele tijd bezig om alle records naar die temp table te kopieren.

Ik heb nu geprobeerd om handmatig het algoritme op MERGE te zetten, daar gaat het fout. MySQL gebruikt dus standaard TEMPTABLE voor deze query.
1
2
3
4
5
6		+---------+------+-------------------------------------------------------------------------------+
| Level   | Code | Message                                                                       |
+---------+------+-------------------------------------------------------------------------------+
| Warning | 1354 | View merge algorithm can't be used here for now (assumed undefined algorithm) |
+---------+------+-------------------------------------------------------------------------------+
1 row in set (0.00 sec)
JeRawoensdag 28 maart 2007 @ 16:08
Die temporary table maakt ie aan omdat ie niet efficiënt gebruik kan maken van je indices, lijkt me. Geef anders eens een vergelijking met de EXPLAIN van de snel draaiende query?
cyberstalkerwoensdag 28 maart 2007 @ 16:19
quote:
Op woensdag 28 maart 2007 16:08 schreef JeRa het volgende:
Die temporary table maakt ie aan omdat ie niet efficiënt gebruik kan maken van je indices, lijkt me. Geef anders eens een vergelijking met de EXPLAIN van de snel draaiende query?
Hier een explain. Ik heb hier wel de indices toegevoegd, zonder enig resultaat.

1
2
3
4
5
6
7
8
9		+----+-------------+---------------------+-------+---------------+----------+---------+--------------------------+------+-------+
| id | select_type | table               | type  | possible_keys | key      | key_len | ref                      | rows | Extra |
+----+-------------+---------------------+-------+---------------+----------+---------+--------------------------+------+-------+
|  1 | SIMPLE      | person              | const | PRIMARY       | PRIMARY  | 4       | const                    |    1 |       |
|  1 | SIMPLE      | peopleStatusOptions | const | PRIMARY       | PRIMARY  | 1       | const                    |    1 |       |
|  1 | SIMPLE      | mScoreCard          | ALL   | PRIMARY       | NULL     | NULL    | NULL                     | 3495 |       |
|  1 | SIMPLE      | mScoreEntry         | ref   | fk_mSCid      | fk_mSCid | 4       | tsu_dev.mScoreCard.mSCid |   15 |       |
|  1 | SIMPLE      | mFaces              | const | fk_pid        | fk_pid   | 4       | const                    |    1 |       |
+----+-------------+---------------------+-------+---------------+----------+---------+--------------------------+------+-------+
JeRawoensdag 28 maart 2007 @ 16:21
mScoreCard en mScoreEntry gebruiken afwisselend wél en géén keys, heb je vantevoren wel een ANALYZE gedaan op alle tabellen?
cyberstalkerwoensdag 28 maart 2007 @ 17:29
Ik heb nu eea aan keys toegevoegd. Ik snap niet waarom, maar dat maakt wel verschil. De query doet er echter nog steeds zo'n 0.30 seconden over.

1
2
3
4
5
6
7
8
9
10
11
12		mysql> explain extended select * from person_info where personid = 851;
+----+-------------+---------------------+--------+-----------------+---------+---------+------------------------------+------+----------------+
| id | select_type | table               | type   | possible_keys   | key     | key_len | ref                          | rows | Extra          |
+----+-------------+---------------------+--------+-----------------+---------+---------+------------------------------+------+----------------+
|  1 | PRIMARY     | <derived2>          | ALL    | NULL            | NULL    | NULL    | NULL                         | 1301 | Using where    |
|  2 | DERIVED     | person              | ALL    | NULL            | NULL    | NULL    | NULL                         | 1306 | Using filesort |
|  2 | DERIVED     | peopleStatusOptions | eq_ref | PRIMARY         | PRIMARY | 1       | tsu_dev.person.status        |    1 |                |
|  2 | DERIVED     | mScoreEntry         | ref    | fk_mSCid,fk_pid | fk_pid  | 4       | tsu_dev.person.pid           |   64 |                |
|  2 | DERIVED     | mScoreCard          | eq_ref | PRIMARY         | PRIMARY | 4       | tsu_dev.mScoreEntry.fk_mSCid |    1 |                |
|  2 | DERIVED     | mFaces              | eq_ref | fk_pid          | fk_pid  | 4       | tsu_dev.person.pid           |    1 |                |
+----+-------------+---------------------+--------+-----------------+---------+---------+------------------------------+------+----------------+
6 rows in set, 83 warnings (0.31 sec)

person heeft gewoon een primary key op pid, dat is het enige veld waarop person gejoined wordt.
JeRawoensdag 28 maart 2007 @ 17:42
Filesorts zijn altijd relatief traag. Wellicht komt dit door de GROUP BY op de tabel person?
cyberstalkerwoensdag 28 maart 2007 @ 18:20
quote:
Op woensdag 28 maart 2007 17:42 schreef JeRa het volgende:
Filesorts zijn altijd relatief traag. Wellicht komt dit door de GROUP BY op de tabel person?
Zonder de GROUP BY kan ik de view inderdaad wel met het MERGE algoritme aanmaken. De view werkt dan ook snel genoeg.

Ik kan dan echter niet het aantal gewerkte dagen opvragen (work_count), tenzij ik hier natuurlijk een functie voor maak (wat me overbodig lijkt). Als ik de query zonder view maak, werkt het sowieso snel, met of zonder GROUP BY. Ik kan overigens nog steeds het MERGE algorithme niet gebruiken in combinatie met een GROUP BY.
cyberstalkerdonderdag 29 maart 2007 @ 10:20
Ik heb het nu opgelost door het aantal dagen in een aparte functie te berekenen. Zo kan ik het aantal gewerkte dagen opvragen zonder GROUP BY en dus ook MERGE gebruiken.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21		CREATE FUNCTION `work_count` (personid INT)
RETURNS INT
READS SQL DATA
BEGIN
    DECLARE worked_days INT;
    SELECT
        COUNT(DISTINCT(mScoreCard.mSCid))
    INTO
        worked_days
    FROM
        mScoreCard
    JOIN
        mScoreEntry
    ON
        mScoreCard.mSCid      = mScoreEntry.fk_mSCid    AND
        mScoreEntry.fk_pid    = personid
    WHERE
        mScoreCard.replacedBy = -1;
    RETURN
        worked_days;
END


Bedankt voor de hulp iedereen (JeRa dus ;) ) .