[PHP] Groot aantal JOINS vs aantal SELECTS

[Verwijderd]

Stel ik heb een groot aantal joins zoals hieronder (er kan er eventueel nog 1 bijkomen)... wat ik me afvroeg is het volgende:

Ik las in de MySQL handleiding dat JOINS in MySQL sterk geoptimaliseerd zijn. SELECTS op zich zijn natuurlijk ook supersnel, maar ik vroeg me af wat er beter (lees: sneller) zou zijn; het gebruik van 1 grote query met een aardig aantal joins (zoals hieronder), of een aantal SELECTS (2 of 3) die net dezelfde gegevens ophalen (die je dan achteraf in een array combineert ofzo) ?

code:

SELECT c.clus_id, c.cluster_name, c.number_of_assignments, c.number_of_choices, s.sugg_id, s.value1, s.value2, s.value3, s.value4, s.max_students, ch.priority, UNIX_TIMESTAMP(ch.submit_date) AS submit_date, u.user_id, u.prenom, u.nom, a.user_id IS NOT NULL AS toekenning, ch.user_id 
    FROM `" . $tbl_cluster . "` c 
        INNER JOIN `" . $tbl_suggestion . "` s 
            ON c.clus_id = s .clus_id 
        LEFT JOIN `" . $tbl_choice . "` ch 
            ON s.sugg_id = ch.sugg_id 
        LEFT JOIN `" . $tbl_user . "` u
            ON ch.user_id = u.user_id 
        LEFT JOIN `" . $tbl_assignment . "` a 
            ON a.sugg_id = ch.sugg_id 
                AND a.user_id = u.user_id 
    WHERE c.proc_id = '" . $_SESSION['procedureId'] . "' 
        " . $cluster_restriction . "
ORDER BY c.position, s.sugg_id, ch.priority, ch.submit_date, ch.user_id

Of valt dat verschil te verwaarlozen denk je?

Alvast bedankt.

[ Voor 5% gewijzigd door Verwijderd op 04-05-2004 10:35 ]

[whoami]

Bekijk eens de verschillende execution plans van de mogelijke queries, en vergelijk deze.

Een LEFT JOIN drukt wel nogal op de performance, en ik zie dat jij al 4 LEFT JOINS staan hebt in 1 query. Je moet je ook altijd afvragen of het nodig is om een OUTER (LEFT of RIGHT) JOIN te gebruiken. Deze zijn altijd trager dan een INNER JOIN.

[Verwijderd]

Alvast bedankt voor de snelle reply!

Met execution plans bedoel je daarmee dat ik de query moet gaan beschrijven door middel van *schrap*DESCRIBE*schrap* EXPLAIN ?

En ik zal zeker eens het verschill tussen OUTER en INNER JOINS erop nalezen.

[ Voor 6% gewijzigd door Verwijderd op 04-05-2004 10:33 ]

[curry684]

whoami schreef op 04 mei 2004 @ 10:19:
Bekijk eens de verschillende execution plans van de mogelijke queries, en vergelijk deze.

Volgens mij doet MySQL dat niet

Een LEFT JOIN drukt wel nogal op de performance, en ik zie dat jij al 4 LEFT JOINS staan hebt in 1 query. Je moet je ook altijd afvragen of het nodig is om een OUTER (LEFT of RIGHT) JOIN te gebruiken. Deze zijn altijd trager dan een INNER JOIN.

De kracht van een join is voor 100% afhankelijk van je indexes. Al join je 100 tabellen: zolang je joint op indexes is het supersnel omdat de filtering reeds gebeurt voordat er feitelijk data wordt gelezen. Om die reden moet je al je keys ook altijd voorzien van een index.

JOINs zijn wat een Relationele Database juist relationeel maken, en daarmee het performance paradepaardje. Altijd joinen op indexed relaties dus als je kunt.

Voorbeeld van een execution plan.

[ Voor 5% gewijzigd door curry684 op 04-05-2004 10:27 ]

[Verwijderd]

Om die reden moet je al je keys ook altijd voorzien van een index.

Gebeurt dat dan al niet automatisch als je een sleutel aanmaakt? ik was toch die mening toegedaan.

Trouwens, alle velden waarop ik JOIN zijn weldegelijk sleutels, daar had ik rekening mee gehouden.

[Janoz]

Wat zou sneller zijn:
1 Gegevens uit de database halen en dit door de speciaal hiervoor ontwikkelde database engine samenvoegen tot 1 resultset.
2 Gegevens uit de database halen en dit allemaal in je php script inlezen om het vervolgens samen te gaan voegen met een geinterpreteerde multi purpose omgeving.

[whoami]

curry684 schreef op 04 mei 2004 @ 10:24:
[...]

Volgens mij doet MySQL dat niet

MySQL heeft toch wel een EXPLAIN PLAN oid ?

De kracht van een join is voor 100% afhankelijk van je indexes. Al join je 100 tabellen: zolang je joint op indexes is het supersnel omdat de filtering reeds gebeurt voordat er feitelijk data wordt gelezen. Om die reden moet je al je keys ook altijd voorzien van een index.

Het is idd wel aangewezen om indexes te leggen op je foreign key velden. Echter, al ligt er een index op je foreign key, een OUTER JOIN zal altijd trager zijn dan een gewone INNER JOIN. Gebruik dus enkel outer joins als dat nodig is.
In dit voorbeeld zie ik 4 OUTER JOINS, en ik vraag me sterk af of het wel nodig is om hier 4 outer joins te gebruiken.

JOINs zijn wat een Relationele Database juist relationeel maken, en daarmee het performance paradepaardje. Altijd joinen op indexed relaties dus als je kunt.

Eensch. Ik zeg ook nergens dat JOINEN minder snel is dan de gegevens samenvoegen in php.
Echter, in sommige gevallen kan het sneller zijn om 2 aparte queries te lanceren dan 1 grote.

[ Voor 9% gewijzigd door whoami op 04-05-2004 10:32 ]

[Verwijderd]

@Janoz

De gegevens zouden sowieso in een multi-dimensional array moeten terechtkomen (door middel van het toepassen groepsbreking onder andere). De afbeelding van de gegevens is TE complex (veel controles) om gewoon de verkregen recordset te loopen. Indien ik meerdere selects heb zou ik dus gewoon een aantal lussen hebben die dezelfde array opvullen.

Ophalen van data (en de paar berekeningen die hiermee gepaar gaan) en het afbeelden blijft zo trouwens mooi gescheiden.

Maar je hebt wel een punt, thx.
Ik ben altijd bereid bij te leren


*Edit*
Trouwens, de eerste LEFT JOIN kan vervagen worden door een INNER JOIN, mijn fout

[ Voor 9% gewijzigd door Verwijderd op 04-05-2004 10:35 ]

[bigbeng]

Nou is het leuke van MySQL dat MyISAM tabellen FK definities negeren (indexen aanleggen kan wel natuurlijk) en dat InnoDB, waar je wel FK's kunt aanleggen, maar dan ben je verplicht om een index op het keyveld aan te leggen.

[cameodski]

bigbeng schreef op 04 mei 2004 @ 10:38:
Nou is het leuke van MySQL dat MyISAM tabellen FK definities negeren (indexen aanleggen kan wel natuurlijk) en dat InnoDB, waar je wel FK's kunt aanleggen, maar dan ben je verplicht om een index op het keyveld aan te leggen.

En wat voor leuks is daar dan aan?
Verplicht een index op een keyveld vind ik helemaal niet zo'n gek idee.

[Verwijderd]

Ook ff vermelden dat het hier snel tussen de 100 -> 1000 records kan gaan.

En moet ik nu nog expliciet een index op mijn sleutelvelden leggen ook (MyISAM)?

Output van de query met het EXPLAIN statement ervoor:
http://www.webcoder.be/lode/_temp/explain.html

[ Voor 64% gewijzigd door Verwijderd op 04-05-2004 10:44 ]

[curry684]

Verwijderd schreef op 04 mei 2004 @ 10:28:
[...]

Gebeurt dat dan al niet automatisch als je een sleutel aanmaakt? ik was toch die mening toegedaan.

Trouwens, alle velden waarop ik JOIN zijn weldegelijk sleutels, daar had ik rekening mee gehouden.

Niet alle sleutels zijn automatisch indexes. SQL Server maakt op een primary key wel automatisch een clustered index aan (helaas, nonclustered zou beter zijn), maar voor een foreign key relatie is in principe geen index nodig.... maar je moet het wel altijd doen

Ik weet niet hoe MySQL ermee om gaat.

[bigbeng]

cameodski schreef op 04 mei 2004 @ 10:42:
[...]

En wat voor leuks is daar dan aan?
Verplicht een index op een keyveld vind ik helemaal niet zo'n gek idee.

Dat je je in je query dus niet druk hoeft te maken over of je keys hebt aangelegd op je FK veld, want dat is dus altijd zo, of je gebruikt MYSQL in non RDBMS mode.

[whoami]

curry684 schreef op 04 mei 2004 @ 11:11:
[...]

Niet alle sleutels zijn automatisch indexes. SQL Server maakt op een primary key wel automatisch een clustered index aan (helaas, nonclustered zou beter zijn), maar voor een foreign key relatie is in principe geen index nodig.... maar je moet het wel altijd doen

Mjah, je kan zelf specifieren of het een clustered of non-clustered index moet zijn.
In sommige gevallen kan je idd beter de non-clustered index op een ander veld leggen.
Het is wel nodig om zorgvuldig je veld te kiezen waarop je die clustered index legt, aangezien een table slechts 1 clustered index kan hebben.
_{maar dat is allemaal een beetje offtopic}

bigbeng schreef op 04 mei 2004 @ 11:13:
[...]


Dat je je in je query dus niet druk hoeft te maken over of je keys hebt aangelegd op je FK veld, want dat is dus altijd zo, of je gebruikt MYSQL in non RDBMS mode.

Ik weet niet hoe MySQL er mee omgaat, maar ik denk niet dat SQL Server en Oracle automatisch een index maken op een foreign key.

[ Voor 22% gewijzigd door whoami op 04-05-2004 11:18 ]

[Verwijderd]

Zozo, heb ergens nog een index bijgelegd op een veld die ook nogal vaak in andere queries gebruikt wordt (en tevens ook in deze).

Daarna ff optimize/analyze enzo gedaan en het resultaat is beter nu, er worden minder rijen overlopen omdat ik nu nog maar in JOIN heb van het type ALL doordat die nieuwe index gebruikt:

EXPLAIN hierzo:
http://www.webcoder.be/lode/_temp/explain_2.html

Geweldig bedankt allen!
Ik denk dat ik toch bij die ene query ga blijven.

Grtz,
db

[ Voor 7% gewijzigd door Verwijderd op 04-05-2004 11:21 ]

[cameodski]

curry684 schreef op 04 mei 2004 @ 11:11:
Niet alle sleutels zijn automatisch indexes. SQL Server maakt op een primary key wel automatisch een clustered index aan (helaas, nonclustered zou beter zijn), maar voor een foreign key relatie is in principe geen index nodig.... maar je moet het wel altijd doen

Ik weet niet hoe MySQL ermee om gaat.

Interessant, waarom vind je die clustered index niet goed? Een PK moet altijd uniek zijn, dus vandaar dat SQL Server daar een unique constraints/index noodzakelijk vindt.

bigbeng schreef op 04 mei 2004 @ 11:13:
[...]


Dat je je in je query dus niet druk hoeft te maken over of je keys hebt aangelegd op je FK veld, want dat is dus altijd zo, of je gebruikt MYSQL in non RDBMS mode.

OK, dus dat vind je leuk. Wat mij alleen niet zo leuk lijkt, is dat je MySQL dus ook erg makkelijk kunt misbruiken als non RDBMS.

[whoami]

cameodski schreef op 04 mei 2004 @ 11:20:
[...]

Interessant, waarom vind je die clustered index niet goed? Een PK moet altijd uniek zijn, dus vandaar dat SQL Server daar een unique constraints/index noodzakelijk vindt.

Een clustered index is niet hetzelfde als een unique index.
Een clustered index bepaald de fysieke volgorde waarin de velden worden opgeslagen. Een clustered index is daarom goed geschikt om op een veld te leggen waarop je 'range-selects' doet.
Echter, als je het veld updated waarop de clustered index ligt, dan moet de DB je index-table gaan aanpassen, wat heel wat tijd kan kosten. Maar een PK wijzig je normaal niet.

[Alex]

Ff een snelheids verhaaltje met indexen. Ik had gister een query met 3 leftjoines op dezelfde tabel, allemaal op de primary key. Nu zat er nog een JOIN op, die een count deed, alleen dit deed hij op een veld zonder index(27500+ records). Gevolg, 14 seconde executie tijd.
Dat veld kreeg vervolgens een index, en we gingen naar de 0,08 seconde voor het uitvoeren van de query.
Dus extra SELECTS zijn niet echt nodig, zolang je maar JOINT e.d. op indexes

[cameodski]

whoami schreef op 04 mei 2004 @ 11:26:
Een clustered index is niet hetzelfde als een unique index.
Een clustered index bepaald de fysieke volgorde waarin de velden worden opgeslagen. Een clustered index is daarom goed geschikt om op een veld te leggen waarop je 'range-selects' doet.
Echter, als je het veld updated waarop de clustered index ligt, dan moet de DB je index-table gaan aanpassen, wat heel wat tijd kan kosten. Maar een PK wijzig je normaal niet.

Ja, dat weet ik gelukkig wel. Mijn vraag over de clustered index staat los van de opmerking op de PK.
En ik heb even op google gezocht naar een verhaaltje over clustered indexen:
http://www.sql-server-performance.com/clustered_indexes.asp
Daarom vraag ik me dus af waarom curry684 niet zoveel van die clustered indexen op PK's moest hebben.

[ Voor 3% gewijzigd door cameodski op 04-05-2004 11:38 ]

[whoami]

cameodski schreef op 04 mei 2004 @ 11:35:
[...]
Daarom vraag ik me dus af waarom curry684 niet zoveel van die clustered indexen moest hebben.

Ik denk dat hij eerder bedoelde dat hij liever geen clustered index op een PK heeft.

Echter, zoals in dat document staat is het idd beter dat je een clustered index legt op een veld dat increment. Op die manier moet de volgorde van de records niet 'gerebuilded' worden.
Als je echter geen range - selects doet op de PK, dan is een clustered index daar misschien onnodig.

[ Voor 34% gewijzigd door whoami op 04-05-2004 11:40 ]

[Verwijderd]

Alex de Groot schreef op 04 mei 2004 @ 11:34:
Ff een snelheids verhaaltje met indexen. Ik had gister een query met 3 leftjoines op dezelfde tabel, allemaal op de primary key. Nu zat er nog een JOIN op, die een count deed, alleen dit deed hij op een veld zonder index(27500+ records). Gevolg, 14 seconde executie tijd.
Dat veld kreeg vervolgens een index, en we gingen naar de 0,08 seconde voor het uitvoeren van de query.
Dus extra SELECTS zijn niet echt nodig, zolang je maar JOINT e.d. op indexes

Mooi zo, praktijkvoorbeelden zeggen nog altijd het meest
Ik denk wel dat ik nu behoorlijk safe zit met mijn query, er wordt altijd gejoined op indexes zodus...

[cameodski]

whoami schreef op 04 mei 2004 @ 11:36:
[...]


Ik denk dat hij eerder bedoelde dat hij liever geen clustered index op een PK heeft.

Ja, dat is wat ik me afvroeg
Over het algemeen wordt geadviseerd om een clustered index op je PK te leggen. Niet voor niks maakt de Enterprise default een clustered index aan.