[SQL] Query optimalisatie probleem (group by)

[vorlox]

Beste,

Ik zit met een probleempje qua optimalisatie van een query.

Even korte uitleg
Ik heb de volgende tabellen
s_fil = bestanden
s_fil_rol_rd = bestand schrijf rollen
s_fol = map namen
s_prt_grp_rol = koppeltabel rollen naar groepen
s_prt_grp_prt = koppeltabel groepen naar gebruikers

Een gebruiker kan dus in 1 of meerdere groepen zitten en elke groep kan 1 of meerdere rollen hebben
Verder heeft een bestand ook 1 of meerdere rollen.

Nu wilde ik dus graag met 1 query alle bestanden waar een gebruiker schrijf rollen voor heeft (of waar geen rollen gedefineerd zijn) ophalen.

Uiteindelijk kwam ik uit bij de volgende query.

code:

    SELECT 
       a.id,
           a.name,
       s_fol.name foldername
    FROM 
       s_fil a
       INNER JOIN s_fol ON a.s_fol_id = s_fol.id
       LEFT OUTER JOIN s_fil_rol_rd b ON a.id = b.s_fil_id
       LEFT OUTER JOIN s_prt_grp_rol d ON b.s_rol_id = d.s_rol_id
       LEFT OUTER JOIN s_prt_grp_prt e ON d.s_prt_grp_id = e.s_prt_grp_id
    WHERE 
      (e.s_prt_id = 3 OR e.s_prt_id IS NULL)
    GROUP BY
        a.id, a.name, s_fol.name
    ORDER BY NULL

Nu is echter de explain output alsvolgt.
Hij gebruikt een temp table

id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	s_fol	index	PRIMARY,id	s_fol_name_idx	768		37	Using index; Using temporary
1	SIMPLE	a	ref	s_fil_s_fol_fk	s_fil_s_fol_fk	8	synone.s_fol.id	5
1	SIMPLE	b	ref	PRIMARY	PRIMARY	8	synone.a.id	1	Using index
1	SIMPLE	d	ref	s_prt_grp_rol_s_rol_fk	s_prt_grp_rol_s_rol_fk	8	synone.b.s_rol_id	1	Using index
1	SIMPLE	e	ref	s_prt_grp_prt_s_prt_grp_fk	s_prt_grp_prt_s_prt_grp_fk	8	synone.d.s_prt_grp_id	1	Using where; Using index

Echter als ik hem zonder de join naar s_fol (mappen) doe dus zoeits als

code:

    SELECT 
       a.id,
           a.name
    FROM 
       s_fil a
       LEFT OUTER JOIN s_fil_rol_rd b ON a.id = b.s_fil_id
       LEFT OUTER JOIN s_prt_grp_rol d ON b.s_rol_id = d.s_rol_id
       LEFT OUTER JOIN s_prt_grp_prt e ON d.s_prt_grp_id = e.s_prt_grp_id
    WHERE 
      (e.s_prt_id = 3 OR e.s_prt_id IS NULL)
    GROUP BY
        a.id, a.name

Dan is de explain alsvolgt

id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	a	index		dsad342	775		273	Using index
1	SIMPLE	b	ref	PRIMARY	PRIMARY	8	synone.a.id	1	Using index
1	SIMPLE	d	ref	s_prt_grp_rol_s_rol_fk	s_prt_grp_rol_s_rol_fk	8	synone.b.s_rol_id	1	Using index
1	SIMPLE	e	ref	s_prt_grp_prt_s_prt_grp_fk	s_prt_grp_prt_s_prt_grp_fk	8	synone.d.s_prt_grp_id	1	Using where; Using index

Nu gebruikt hij geen temp table

Kan het komen doordat ik in de group by (groepeer op kolommen uit 2 tabellen dat mysql daar dus altijd een temp van moet maken)
of is er een andere manier om dit te voorkomen

Ik heb het net ook geprobeerd via een subquery....maar dit duurt meteen 1.4 sec in MySQL

code:

    SELECT
       a.id,
           a.name,
       s_fol.name foldername
    FROM 
       s_fil a
       INNER JOIN s_fol ON a.s_fol_id = s_fol.id
    WHERE a.id IN (
    
        SELECT 
           a.id
        FROM 
           s_fil a
           LEFT OUTER JOIN s_fil_rol_rd b ON a.id = b.s_fil_id
           LEFT OUTER JOIN s_prt_grp_rol d ON b.s_rol_id = d.s_rol_id
           LEFT OUTER JOIN s_prt_grp_prt e ON d.s_prt_grp_id = e.s_prt_grp_id
        WHERE 
          (e.s_prt_id = 3 OR e.s_prt_id IS NULL)
        GROUP BY
            a.id
    )

id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	PRIMARY	s_fol	index	PRIMARY,id	s_fol_name_idx	768		37	Using index
1	PRIMARY	a	ref	s_fil_s_fol_fk	s_fil_s_fol_fk	8	synone.s_fol.id	5	Using where
2	DEPENDENT SUBQUERY	a	index		PRIMARY	8		273	Using index
2	DEPENDENT SUBQUERY	b	ref	PRIMARY	PRIMARY	8	synone.a.id	1	Using index
2	DEPENDENT SUBQUERY	d	ref	s_prt_grp_rol_s_rol_fk	s_prt_grp_rol_s_rol_fk	8	synone.b.s_rol_id	1	Using index
2	DEPENDENT SUBQUERY	e	ref	s_prt_grp_prt_s_prt_grp_fk	s_prt_grp_prt_s_prt_grp_fk	8	synone.d.s_prt_grp_id	1	Using where; Using index

[ Voor 22% gewijzigd door vorlox op 07-03-2009 14:53 ]

[Miyamoto]

Geen antwoord op je vraag, maar ik dacht dat SQL een beschrijvende taal was, waarbij het dus niet uitmaakt hoe de query geformuleerd wordt?

[vorlox]

Als je een fatsoenlijke database hebt is dat misschien zo, maar ik draai MySQL

Het gaat erom hoe je query uitgevoerd wordt door de database, de database maakt een soort plan van aanpak aan de hand van je query...en soms kun je dat een beetje beinvloeden door de query anders op te bouwen. Vandaar de explain syntax om dit te controleren

[ Voor 62% gewijzigd door vorlox op 07-03-2009 14:54 ]

[Hydra]

Miyamoto schreef op zaterdag 07 maart 2009 @ 14:24:
Geen antwoord op je vraag, maar ik dacht dat SQL een beschrijvende taal was, waarbij het dus niet uitmaakt hoe de query geformuleerd wordt?

Moet je eens met Oracle bezig gaan, daar moet je veelal met 'hits' aangeven dat 'ie eerst op basis van de keys een zo klein mogelijke set op bouwt, en pas daarna de rest van het werk doet. Begin nu ook te begrijpen waarom Oracle DBA's zoveel verdienen: het is kutwerk en je hebt ze echt nodig

[winkbrace]

Wat betreft je vraag:
Ik ben geen MySQL expert, maar je zou om je eigen vraag te beantwoorden eens kunnen proberen hoe je explain plan eruitziet zonder GROUP BY in je query. Verder ben ik wel benieuwd of het maken van een temp table de query trager maakt? Ik kan het wel begrijpen dat 'ie standaard een kleinere verzameling maakt door de inner join selectie eerst te doen en daar vervolgens dan de outer joins tegenaan te hangen over de index. Want wanneer je over de index gaat, moet je elk record er in opzoeken, dus zou het sneller kunnen zijn dat aantal eerst te reduceren.

Wat betreft de reacties:
@rooot: Het maakt alles uit hoe je je SQL statement schrijft!
Vergelijk deze twee queries maar even. Ze geven exact hetzelfde resultaat, maar de doorlooptijd zal aanzienlijk verschillen

SQL:

select id
,      (select datum
        from   big_table t2
        where  t.id = t2.id
       ) datum
from   big_table t

select id
,      datum
from   big_table t

Verder begrijp ik de sneer van Hydra naar Oracle niet. Oracle is juist behoorlijk intelligent in het zo efficiënt mogelijk uitvoeren van de query's. Gelukkig hebben ze je wel de mogelijkheid gegeven om door middel van 'hints' het explain plan bij te sturen mocht dat nodig zijn. In 90% van de gevallen echter is de query gewoon slechts geschreven (of soms is de database verkeerd ingericht)

[Verwijderd]

Ik zou sowieso geen joins gebruiken als ik jou was, dat neemt veel meer tijd in beslag.

[RobIII]

Verwijderd schreef op maandag 09 maart 2009 @ 15:24:
Ik zou sowieso geen joins gebruiken als ik jou was, dat neemt veel meer tijd in beslag.

Want? Heb je daar bronnen voor
Klinkklare onzin. Joins zijn er niet voor niets in een RDBMS.

[Voutloos]

En in de context van mysql is een join juist meestal de snellere optie tov. IN() en subqueries.

[_js_]

MySQL kan maar 1 index per tabel gebruiken in een query.
Om de s_fol tabel te joinen met a moet er naar het juiste id gezocht worden, hier zou een index op id dus kunnen helpen. Voor de group by op name is weer een index op name nodig. Als je zonder tijdelijke tabel wilt werken zou je een index over de beide kolommen name en id kunnen maken..