[MySQL] Inefficiente view

[cyberstalker]

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:

SQL:

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,
    DATE_FORMAT(FROM_DAYS(TO_DAYS(NOW())-TO_DAYS(person.birthdate)), '%Y')+0    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

SQL:

SELECT * FROM person_info where personid = 851;

Dit werkt, maar is vreselijk traag. Wanneer ik nu als query het volgende opgeef:

SQL:

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,
    DATE_FORMAT(FROM_DAYS(TO_DAYS(NOW())-TO_DAYS(person.birthdate)), '%Y')+0    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.

MySQL versie is mysql-5.0.26

[sky-]

Lijkt me duidelijk ?

Wat is je vraag precies.

[cyberstalker]

k8skaaay schreef op woensdag 28 maart 2007 @ 11:17:
Lijkt me duidelijk ?

Wat is je vraag precies.

Hoe ik de view op een werkbare snelheid krijg. Liefst net zo snel als wanneer ik de query handmatig invoer.

[cyberstalker]

Ik ben er nu iig achter waarom de query zo langzaam werkt. MySQL gebruikt een temporary table voor deze view. Wanneer ik het algoritme handmatig op MERGE probeer te zetten krijg ik een warning:

code:

+---------+------+-------------------------------------------------------------------------------+
| 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)

Sommige van de tabellen in de query hebben flink wat records. Waarschijnlijk kost het MySQL veel tijd om die tabellen te kopieren naar een tijdelijke tabel.

[Knutselsmurf]

Zoiets heb ik ook ooit bij de hand gehad. Het blijkt dat MySQL bij dit soort queries (dus met een join) altijd een temptable gebruikt en je dus de indices op de achterliggende tabellen kwijt bent.

Het merge-algorithme is voor andere doeleinden.
Die wordt gebruikt als je twee tabellen hebt, met dezelfde opbouw. Bijvoorbeeld 1 voor archief en 1 voor de actuele data. Deze twee tabellen worden dan met een andere storage-engine opgeslagen.

Om in alle data te zoeken, maak je dan een view :

SQL:

select * from actueel union select * from archief;

Met het merge-algorithme kan je dan wel de achterliggende indices gebruiken.

Zolang dit niet verbetert, zijn views in MySQL in mijn ogen een no-go.

[KabouterSuper]

Het probleem lijkt me inderdaad dat mysql de indices van de onderliggende tabellen niet kan gebruiken. Dit is overigens niet mysql-specifiek.....Oracle heeft last van hetzelfde probleem. Hier is weinig aan te doen.

[LauPro]

Het idee van de view is toch juist dat je zelf binnen je view weer nieuwe indices maakt?

[cyberstalker]

Na het aanmaken van een aantal indices werkt het wonder boven wonder een stuk sneller met de view. Het wonderlijke hieraan vind ik dat het zonder die extra indices zonder view wel snel werkt.

Het werkt hoe dan ook nog steeds niet zo snel wanneer ik de view gebruik. Wanneer ik de GROUP BY weghaal (en daarmee ook de work_count, want die werkt uiteraard niet zonder, omdat ik COUNT gebruik) kan ik de view wel maken met ALGORITHM=MERGE. De view werkt dan wel op goede snelheid.

Hoe kan ik die work_count zo invoegen dat ik wel ALGORITHM=MERGE kan gebruiken in mijn view?

[cyberstalker]

Ik heb het probleem nu opgelost door het aantal dagen vanuit een aparte functie te berekenen. Zo kan ik het aantal dagen in de view weergeven zonder GROUP BY (en dus MERGE gebruiken).

Ter referentie, ik heb de volgende functie gemaakt:

SQL:

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

De view werkt nu prima. Het is echter nog niet zoals ik het zou willen hebben (een functie voor iets wat simpel genoeg in die query kan heb ik liever niet), dus mocht iemand nog een echte oplossing weten, dan hou ik me aanbevolen .

I.i.g. bedankt voor alle hulp.