[MySQL] 2 left joins levert trage queries; 1 left join snel

[js303]

hoi, ik heb de volgende query:

SQL:

SELECT 
main.id 'main_object_id',
COUNT(DISTINCT art.id) 'related_articles',
COUNT(DISTINCT rel.object_id) 'related_objects'

FROM objects 'main'
LEFT JOIN articles 'art' ON art.object_id = main.id
LEFT JOIN objects_rel 'rel' ON rel.object_source_id = main.id

WHERE main.name LIKE 'bouw%'
GROUP BY main.id

Resultset levert circa 30 results, met per record gemiddeld 400 artikelen en 700 gerelateerde objecten. Als ik DISTINCT echter weglaat, wordt duidelijk hoe de JOINs uitgevoerd worden: per hoofdrecord (1 van de 30) worden eerst de 400 artikelen-rijen opgehaald en opgeteld. Vervolgens voor elk artikel-rij een objecten-rij opgehaald: resultaat: 400x700 = 280000 gevonden artikelen evenals gerelateerde objecten.

Hoe kan ik MySQL (4.1) nou vertellen dat-ie voor elk van de 30 hoofdresultaten 1x 400 artikelen ophaal en 1x 700? Of ben ik genoodzaakt de query als 3 losse queries te doen - eerst de hoofdquery, vervolgens een query voor alle gerelateerde artikelen bij de 30 hoofdrijen, tot slot een query voor alle gerelateerde objecten bij de 30 hoofdrijen?

[ Voor 7% gewijzigd door js303 op 22-11-2007 16:13 ]

[Verwijderd]

Eerlijk gezegd kan ik hem niet helemaal volgens wat je bedoelt, maar als dit echt traag is (hoe lang duurt de query precies?) ligt het denk ik aan je keys.

[Voutloos]

Als je join condities (en je relaties) goed zijn heb je gewoon geen cartesisch prodcut met dergelijke query.

Verder moet je voor performance problemen oa. een blik op de explain van je query werpen.

[js303]

hoi, ik had de query niet goed overgenomen in m'n topic, net aangepast, nu staat-ie er zoals ik bedoel. m'n keys / indexen staan goed - via EXPLAIN krijg ik de melding dat van de left joins 'ref' type indexen worden gebruikt.

maar wat ik al zeg: het gaat niet zozeer om indexen die niet worden gebruikt, maar om 't feit dat deze query de left joins schakelt, waardoor er 400 x 700 rijen per hoofd-rij worden geJOINed...

[Voutloos]

Je bent toch echt onduidelijk in je vraagstelling, maar ik vermoed dat je stiekem gewoon GROUP BY main.id, art.id wil doen.

[js303]

OK, hier even de daadwerkelijke query (wat minder illustratief) - een aantal varianten met en zonder de LEFT JOINs.

haal alleen naampjes op ~ 0.08 seconden

SQL:

    SELECT SQL_NO_CACHE
        orel.type_id, orel.name1, orel.name2, orel.name3, orel.initials, orel.title, orel.header, orel.function, orel.publish, 
        t.plural 'grouplabel', t.id 'group_id', orel.id, CONCAT(orel.name1, IF(LENGTH(orel.name2)>0, CONCAT(', ',orel.name2), '')) 'label'

    FROM ws_objects orel, ws_objects_types t 
    WHERE t.id = orel.type_id AND orel.original_object_id = 0 AND (orel.name1 LIKE '%netherlands%' OR orel.name2 LIKE '%netherlands%') 
    ORDER BY t.plural ASC, orel.name1 ASC, orel.name2 ASC 
    LIMIT 0, 100

haal naampjes en related articles op ~ 0,08 seconden

SQL:

    SELECT SQL_NO_CACHE
        orel.type_id, orel.name1, orel.name2, orel.name3, orel.initials, orel.title, orel.header, orel.function, orel.publish, 
        t.plural 'grouplabel', t.id 'group_id', orel.id, CONCAT(orel.name1, IF(LENGTH(orel.name2)>0, CONCAT(', ',orel.name2), '')) 'label',
        COUNT(DISTINCT oc.id) 'numarticles'

    FROM ws_objects orel, ws_objects_types t 
    LEFT JOIN ws_objects_content oc ON oc.object_id = orel.id AND oc.article_id != 0 
    WHERE t.id = orel.type_id AND orel.original_object_id = 0 AND (orel.name1 LIKE '%netherlands%' OR orel.name2 LIKE '%netherlands%') 
    GROUP BY orel.id 
    ORDER BY t.plural ASC, orel.name1 ASC, orel.name2 ASC 
    LIMIT 0, 100

haal naampjes en related objects op ~ 0,09 seconden

SQL:

    SELECT SQL_NO_CACHE
        orel.type_id, orel.name1, orel.name2, orel.name3, orel.initials, orel.title, orel.header, orel.function, orel.publish, 
        t.plural 'grouplabel', t.id 'group_id', orel.id, CONCAT(orel.name1, IF(LENGTH(orel.name2)>0, CONCAT(', ',orel.name2), '')) 'label',
        COUNT(DISTINCT rl.id) 'numrelations' 

    FROM ws_objects orel, ws_objects_types t 
    LEFT JOIN ws_objects_rel rl ON rl.object_id = orel.id 
    WHERE t.id = orel.type_id AND orel.original_object_id = 0 AND (orel.name1 LIKE '%netherlands%' OR orel.name2 LIKE '%netherlands%') 
    GROUP BY orel.id 
    ORDER BY t.plural ASC, orel.name1 ASC, orel.name2 ASC 
    LIMIT 0, 100

haal naampjes, related articles, related objects ~ 9 seconden

SQL:

    SELECT SQL_NO_CACHE
        orel.type_id, orel.name1, orel.name2, orel.name3, orel.initials, orel.title, orel.header, orel.function, orel.publish, 
        t.plural 'grouplabel', t.id 'group_id', orel.id, CONCAT(orel.name1, IF(LENGTH(orel.name2)>0, CONCAT(', ',orel.name2), '')) 'label', 
        COUNT(DISTINCT oc.id) 'numarticles', 
        COUNT(DISTINCT rl.id) 'numrelations' 

    FROM ws_objects orel, ws_objects_types t 
    LEFT JOIN ws_objects_content oc ON oc.object_id = orel.id AND oc.article_id != 0 
    LEFT JOIN ws_objects_rel rl ON rl.object_id = orel.id 
    WHERE t.id = orel.type_id AND orel.original_object_id = 0 AND (orel.name1 LIKE '%netherlands%' OR orel.name2 LIKE '%netherlands%') 
    GROUP BY orel.id 
    ORDER BY t.plural ASC, orel.name1 ASC, orel.name2 ASC 
    LIMIT 0, 100

oftewel zodra ik ze allebei JOIN, krijg ik gelazer.

[Verwijderd]

Misschien is het handig om uit te leggen wat voor info je nou eigenlijk wil ophalen, want dat vat ik niet helemaal eigenlijk

[ Voor 7% gewijzigd door Verwijderd op 22-11-2007 16:32 ]

[js303]

@Voutloos: wat betreft groeperen heb je gelijk dat dat op main.id moet, maar als ik groepeer op bijv. art.id, dan krijg ik alle individuele artikelen terug, terwijl ik alleen wil weten hoeveel artikelen en hoeveel gerelateerde objecten aan elk object hangen. wellicht dat je m'n openings-post al had gelezen voordat ik hem had aangepast (zat voutje in), kijk er nog eens naar als je wil.

[KabouterSuper]

Je doet effectief een uitproduct op articles en objects_rel, en verbergt dit met een distinct. Dit is inderdaad heel duur. Beter is het om twee queries te draaien (objects+articles en objects+objects_rel). Je kunt dit eventueel met een union samenvoegen tot 1 query. Echter, als je de twee regels wilt combineren tot 1 regel heb je subquery functionaliteit nodig. Ik dacht dat MySQL 4 dit nog niet ondersteunt.

[js303]

@Herelam en de rest:

ik wil alle objecten tonen op basis van een zoekterm, en aan deze objecten het aantal gerelateerde artikelen evenals het aantal gerelateerde objecten. dus zoiets:

#   object     aantal_artikelen    aantal_objecten
1   bouwfonds  160                 32
2   rodamco    55                  847

[js303]

@ kaboutersuper: precies, dat is wat ik doe, de DISTINCT verbergt dit, kwam er ook pas achter wat de veroorzaker van de vertraging was toen ik DISTINCT weghaalde.
aha, toch een UNION dus (of meerdere queries). volgens mij heeft 4.1 wel UNION. ik ga ff de manual checken.

[KabouterSuper]

UNION is geen probleem in MySQL 4. En subqueries worden vanaf MySQL 4.1 ondersteund volgens MySQL , zie http://dev.mysql.com/doc/refman/5.0/en/subqueries.html. Dan kan je de twee regels mergen tot 1 regel door

SQL:

select id, sum(art), sum(obj)
from
(
select id, count(1) art,0 obj from .... group by id
union
select id, 0 art,count(1) obj from ... group by id
)
group by id

[Voutloos]

js303 schreef op donderdag 22 november 2007 @ 16:39:
@Voutloos: wat betreft groeperen heb je gelijk dat dat op main.id moet, maar als ik groepeer op bijv. art.id, dan krijg ik alle individuele artikelen terug,

Ik noemde een group by op beiden.

Maar goed, ik blijf bij mij originele opmerking over cartesisch producten, explain en het onduidelijk zijn. En de where t.id = orel.type_id clause moet natuurlijk een join conditie zijn.

[AaiGht]

op school hebben wij ook queries bij acces, is dit hetzelfde?

Die ow van twee dagen terug was niet voldoende zeker? Je zou moeten weten dat dit ongewenst is

[ Voor 45% gewijzigd door Creepy op 26-11-2007 13:25 ]

[js303]

@KabouterSuper, je aanzetje heeft me enorm geholpen. Aanvankelijk kwam ik er niet uit en heb stap voor stap de mogelijkheden bekeken, maar is toch gelukt. Weet niet of het slimmer kan dan wat ik nu heb, maar dit is het geworden:

SQL:

SELECT 
    object_id, 
    SUM(numarticles), 
    SUM(numobjects) 

FROM ( 
    SELECT 
        o.id 'object_id',
        COUNT(oc.article_id) 'numarticles',
        0 'numobjects'

    FROM ws_objects o, ws_objects_content oc
    WHERE (o.name1 LIKE '%netherlands%' OR o.name2 LIKE '%netherlands%') 
    AND o.original_object_id = 0 
    AND oc.object_id = o.id 
    AND oc.article_id != 0
    GROUP BY o.id

    UNION 

    SELECT 
        o.id 'object_id',
        0 'numarticles',
        COUNT(rl.id) 'numobjects'

    FROM ws_objects o, ws_objects_rel rl
    WHERE (o.name1 LIKE '%netherlands%' OR o.name2 LIKE '%netherlands%') 
    AND o.original_object_id = 0 
    AND rl.object_id = o.id 
    GROUP BY o.id

    UNION 

    SELECT 
        o.id 'object_id',
        0 'numarticles',
        0 'numobjects'

    FROM ws_objects o
    WHERE (o.name1 LIKE '%netherlands%' OR o.name2 LIKE '%netherlands%') 
    AND o.original_object_id = 0 
) AS alles

GROUP by object_id

Query geeft dus alle objecten die de naam "netherlands" bevatten, en het aantal related artikelen of 0 evenals het aantal related objecten of 0. De laatste subquery in de UNION zorgt ervoor dat ook alle objecten gevonden worden die eventueel 0 related objecten en artikelen hebben.

[ Voor 10% gewijzigd door js303 op 26-11-2007 12:55 ]

[js303]

OK, ik heb 'em nog wat sneller en compacter gekregen middels een LEFT JOIN voor de (eventuele) related articles. Scheelt weer een SELECT LIKE subquery, levert ongeveer 20% snelheidswinst op:

SQL:

SELECT
    object_id, 
    SUM(numarticles), 
    SUM(numobjects) 

FROM ( 
    SELECT 
        o.id 'object_id',
        COUNT(oc.article_id) 'numarticles',
        0 'numobjects'

    FROM ws_objects o
    LEFT JOIN ws_objects_content oc ON oc.object_id = o.id AND oc.article_id != 0
    WHERE (o.name1 LIKE '%netherlands%' OR o.name2 LIKE '%netherlands%') 
    AND o.original_object_id = 0 
    GROUP BY o.id

    UNION 

    SELECT 
        o.id 'object_id',
        0 'numarticles',
        COUNT(rl.id) 'numobjects'

    FROM ws_objects o, ws_objects_rel rl
    WHERE (o.name1 LIKE '%netherlands%' OR o.name2 LIKE '%netherlands%') 
    AND o.original_object_id = 0 
    AND rl.object_id = o.id 
    GROUP BY o.id

) AS alles

GROUP by object_id

Blijft alleen 'zonde' dat ik nog steeds 2x de LIKE selects moet doen, maar krijg het niet meer ingedikt dan dit.

[ Voor 8% gewijzigd door js303 op 26-11-2007 13:00 ]

[KabouterSuper]

De query ziet er goed uit zo.....niets meer aan doen!

Je zou eventueel nog kunnen discussieren over COUNT(<kolom>) versus SUM(1) en UNION versus UNION ALL, maar dat vind ik geneuzel in de marge

Uit interesse: hoe snel is de query nu?

[js303]

De query is nu tussen de 0.28 en 0.32 sec, nog steeds wel een m'n langzamere queries in verhouding op deze server (probeer nooit hoger dan de 0.05 te komen per query) maar dat komt natuurlijk door mijn dubbele LIKE % OR LIKE %. En nog altijd enorm veel beter dan die 9 seconden van eerst, btw het is voor een admin-gedeelte en niet voor de front-end.

Ik zou de resulterende object-id's van de eerste LIKE select nog in een temptable (HEAP) kunnen zetten en deze dan gebruiken ipv een 2e LIKE - kijken of dat wat oplevert, maar moet nu even door met m'n werk.

Ik kan in elk geval weer verder, thanx!

[ Voor 35% gewijzigd door js303 op 26-11-2007 14:09 ]