[MySQL 5] Optimalisatie: Subqueries of losse queries?

[BikkelZ]

Deze query doet er 3.7773 sec over:

SQL:

SELECT it.id, it.type, max( if( it.id = icr.child_item_id
AND parent_item_id =77, 1, 0 ) ) AS `is parent` , max( if( it.id = icr.parent_item_id
AND child_item_id =77, 1, 0 ) ) AS `is child` , max( if( it.id = icr.child_item_id
AND parent_item_id !=77, 1, 0 ) ) AS `is sibling`
FROM item_type it, item_child_restriction icr
WHERE
(
    it.id = icr.child_item_id
    AND parent_item_id =77
)
OR
(
    (
        child_item_id IN
        (

            SELECT child_item_id
            FROM item_child_restriction
            WHERE parent_item_id IN
            (

                SELECT parent_item_id
                FROM item_child_restriction
                WHERE parent_item_id =19
            )
        )
    )
    AND
    (
        (
            it.id = icr.parent_item_id
            AND child_item_id =77
        )
        OR
        (
            it.id = icr.child_item_id
            AND parent_item_id !=77
            AND parent_item_id IN
            (
                SELECT parent_item_id
                FROM item_child_restriction
                WHERE
                (
                    child_item_id =77
                )
            )
        )
    )
)
GROUP BY it.type

Als ik die twee subqueries apart uitvoer, en de resultaten als verzameling opgeef, doet hij er nog maar 0.0170 sec over:

SQL:

SELECT  it.id,
    it.type,
    max( if( it.id = icr.child_item_id AND  parent_item_id = 15, 1, 0 ) ) AS `is parent`,
    max( if( it.id = icr.parent_item_id AND  child_item_id = 15, 1, 0 ) ) AS `is child`,
    max( if( it.id = icr.child_item_id AND  parent_item_id != 15, 1, 0 ) ) AS `is sibling`
FROM item_type it, item_child_restriction icr
WHERE
(
    it.id = icr.child_item_id
    AND  parent_item_id = 15
)
OR
(
    (
        child_item_id IN (7, 14, 15, 16, 17, 20, 23, 33, 34, 36, 38, 46, 48, 56, 61, 62, 63, 64, 65, 66, 70, 77)
    )
    AND
    (
        (
            it.id = icr.parent_item_id
            AND  child_item_id = 15
        )
        OR
        (
            it.id = icr.child_item_id
            AND   parent_item_id != 15
            
            AND parent_item_id IN
            (
                1, 7, 15, 17, 19, 20, 21, 22, 23, 51, 52, 56, 58, 70, 71
            )
                    
        )
    )
)
GROUP BY it.type

En de subqueries die vervangen zijn gaan nog sneller dan dat, dus het is gewoon honderd keer zo snel. Nou houd ik er zelf van om alle logica op een zelfde plek te houden, en niet te verspreiden door scripts heen, kortom, het liefste een query per blok data.

Ik denk zelf dat MySQL de mist in gaat doordat het steeds opnieuw die subquery uitvoert in plaats van het als een vaste verzameling te zien (wat het duidelijk gewoon is), is er een manier om er voor te zorgen dat de interpreter het wel zo ziet waardoor ik niet meer met losse queries hoef te werken?

Edit:

EXPLAIN resultaat van de eerste query:

code:

1   PRIMARY     it      index   PRIMARY     type    22      NULL    78      Using index; Using temporary; Using filesort
1   PRIMARY     icr     index   PRIMARY,parent_item_id,child_item_id    parent_item_id  4   NULL    1723    Using where; Using index
4   DEPENDENT SUBQUERY  item_child_restriction  unique_subquery     PRIMARY,parent_item_id,child_item_id    PRIMARY     8   func,const  1   Using index; Using where
2   DEPENDENT SUBQUERY  item_child_restriction  index_subquery  child_item_id   child_item_id   4   func    10  Using index; Using where
3   DEPENDENT SUBQUERY  item_child_restriction  ref     PRIMARY,parent_item_id  parent_item_id  4   const   22  Using where; Using index

[ Voor 12% gewijzigd door BikkelZ op 06-12-2006 12:41 ]

[NMe]

Heb je wel eens geprobeerd om je subqueries om te schrijven naar een join-oplossing? Je kan ook eens kijken of je queries wel netjes gecached worden.

[BHR]

Ik weet niet welke versie van mysql 5.x je precies gebruikt, maar zie http://bugs.mysql.com/bug.php?id=9090.

Komt er op neer dat mysql niet goed om gaat met:

SQL:

parent_item_id IN
            (
                SELECT parent_item_id
                FROM item_child_restriction
                WHERE
                (
                    child_item_id =77
                )
            ) 

Hier maakt mysql een DEPENDENT SUBQUERY van (zoals je in explain ziet). Dat is wat het zo langzaam maakt.

zie ook: http://dev.mysql.com/doc/...orrelated-subqueries.html

Wat soms wel werkt (afhankelijk van complexiteit query) is om de subquery als tabelreferentie op te nemen:

SQL:

SELECT *
FROM item_type it, item_child_restriction icr,
(
                SELECT parent_item_id
                FROM item_child_restriction
                WHERE
                (
                    child_item_id =77
                )
) as picr
WHERE ....
AND icr.parent_item_id = picr.parent_item_id
...

Helaas maakt mysql dan echter geen gebruik meer van indexes op parent_item_id voor de betreffende join.

[ Voor 37% gewijzigd door BHR op 06-12-2006 13:39 ]

[BikkelZ]

-NMe- schreef op woensdag 06 december 2006 @ 12:42:
Heb je wel eens geprobeerd om je subqueries om te schrijven naar een join-oplossing? Je kan ook eens kijken of je queries wel netjes gecached worden.

Ik liep al met die gedachte te spelen, ik ga er morgen eens een variant van schrijven.

Maar je bedoelt zoiets als:

SQL:

SELECT it.id, it.type, max( if( it.id = icr.child_item_id
AND parent_item_id =77, 1, 0 ) ) AS `is parent` , max( if( it.id = icr.parent_item_id
AND child_item_id =77, 1, 0 ) ) AS `is child` , max( if( it.id = icr.child_item_id
AND parent_item_id !=77, 1, 0 ) ) AS `is sibling`
FROM item_type it, item_child_restriction icr 
JOIN item_child_restriction AS check_parent_restriction ON (wat nu in where staat bij de subquery) JOIN  item_child_restriction AS check_parent  ON (wat nu in de subsubquery bij where staat) etc ..... 

Is dit overigens 'typisch MySQL' of hebben alle DB-engines dit soort problemen? Ik weet namelijk wel dat subqueries schrijven zo'n beetje gepresenteerd werd als dé manier om data uit verschillende tabellen te combineren als het ingewikkeld werd op HBO. Beetje een kwalijke zaak als het al niet vooruit te branden is op een betrekkelijk kleine DB als dit......

[Confusion]

BikkelZ schreef op woensdag 06 december 2006 @ 17:28:
Beetje een kwalijke zaak als het al niet vooruit te branden is op een betrekkelijk kleine DB als dit......

Voor versie 5 had MySql niet eens subqueries. Het was nog steeds goed bruikbaar voor veel dingen.

[NMe]

BikkelZ schreef op woensdag 06 december 2006 @ 17:28:
Is dit overigens 'typisch MySQL' of hebben alle DB-engines dit soort problemen? Ik weet namelijk wel dat subqueries schrijven zo'n beetje gepresenteerd werd als dé manier om data uit verschillende tabellen te combineren als het ingewikkeld werd op HBO. Beetje een kwalijke zaak als het al niet vooruit te branden is op een betrekkelijk kleine DB als dit......

Joins zijn veel gangbaarder dan subqueries, in elk geval in MySQL.

[Verwijderd]

-NMe- schreef op woensdag 06 december 2006 @ 18:08:
Joins zijn veel gangbaarder dan subqueries, in elk geval in MySQL.

In dit geval in het trouwens ook nog niet eens een 'gewone' subquery maar een zogenaamde scalair subquery (als value in de where clause dus). De DB planner zou makkelijk moeten zien dat er hier een materialize gedaan moet worden. Als ik een vergelijkbare query op postgresql draai gebeurd dat wel gewoon.

Als alternatief kun je een join maken met diezelfde verzameling waarbij je de waardes dan grouped naar een SQL Array (bv array_accum aggregator). Vervolgens kun je eenvoudig in je where clause met de subset operator ( <@ ) controleren of het juiste id in de verzameling zit.

Als je meerdere waardes wilt checken kan dit zelfs een stuk sneller zijn, omdat je ook op combinaties kunt zoeken, bv '{1,2}' <@ '{1,2,7}' oid.


Schematisch:

SQL:

select id, ...
from tabel1
left outer join
   ( select id, array_accum(rst) as rst from tabel2 group by id ) as tabela2
on (id)
where tabel1.cid <@ tabela2.rst

[DexterDee]

Confusion schreef op woensdag 06 december 2006 @ 17:32:
[...]

Voor versie 5 had MySql niet eens subqueries. Het was nog steeds goed bruikbaar voor veel dingen.

Dat is niet waar. Subqueries werken vanaf MySQL 4.1.
Views, triggers en stored procedures zijn nieuw in MySQL 5

just my

[BikkelZ]

Is het misschien een beter idee om over te stappen naar PostgreSQL nu ik nog in dit vroege stadium zit?

Ik gebruik wel vaker behoorlijk grote queries verderop in dit project, en ik heb eigenlijk niet zo veel trek om het allemaal om te tikken naar wazige JOIN-constructies omdat dat gewoon niet lekker leest als ik over twee jaar nog eens iets moet gaan veranderen.

Ik lever graag wat performance in als dat betekent dat ik een robuustere performance heb als ik wat dieper duik dan "SELECT * FROM pagina JOIN formulierveldjes ON pagina.id = formulierveldjes.pagina_id WHERE page = 1".

Liefst zou ik ook nog met triggers, echte foreign keys etc. werken, ik begin eigenlijk al weer bang te worden dat het er wel in zit, maar vraag niet hoe....

[Janoz]

ALs je de mogelijkheid hebt om het te doen zou ik het zeker niet laten en zelfs aanraden. MySQL mag dan wel een veel gebruikte database zijn, maar zeker niet de beste. Zeker omdat je redelijk ingewikkelde queries hebt en gebruik wilt maken van FK's en triggers. Die dingen zitten pas net in MySQL. Naast dat ze dus nog best wel ietsje buggy kunnen zijn (want nog neit uit ontwwikkeld) zijn het ook achteraf toegevoegde features. Daarmee wil ik niet zeggen dat ze er maar ingehacked zijn, maar wanneer er vanav dag 1 rekening mee gehouden zou zijn zou ik meer vertrouwens in de robuustheid hebben.

*insert famous manual quote over de reden om geen FK's te implementeren*

[BikkelZ]

Ik zal eens gaan lobbyen voor PostgreSQL.