query optimalisatie
Stel ik heb de volgende query
Code (php)
1
2
3
4
5
6
7
8
2
3
4
5
6
7
8
SELECT pers_achternaam, pers_voornaam, afdeling_naam
FROM zh_afdeling a, personeel p
WHERE a.afdeling_nr = p.pers_afd_toegewezen
AND pers_achternaam >= 'S'
AND afdeling_naam = 'Radiologie'
FROM zh_afdeling a, personeel p
WHERE a.afdeling_nr = p.pers_afd_toegewezen
AND pers_achternaam >= 'S'
AND afdeling_naam = 'Radiologie'
wat is nou de volgorde van stappen die je zou volgen?
Een string vergelijken met meer dan S... werkt dat?
lijkt mij langzaam... beter pak je van de kolom de eerste letter als nieuwe kolom?
Afdeling als string? Geen tabel met afdelingen?
In dit geval heb ik geen idee welk resultaat je met je query wilt bereiken, dus is het ook niet mogelijk om te bepalen hoe dit resultaat in een zo kort mogelijke tijd te bereiken.
Gezien het query deel dat je al geplaatst hebt zou ik je willen adviseren om gebruik te maken van een JOIN.
Toevoeging op 20/10/2013 18:22:26:
in dit geval haal ik alle gebruikers op waarvan de achternaam met een S begint of een letter na de S en vanuit de Radialogie afdeling komt.
Pipo Clown op 20/10/2013 18:20:30:
Gezien het query deel dat je al geplaatst hebt zou ik je willen adviseren om gebruik te maken van een JOIN.
Je doet je naam eer aan, leg mij uit waar de join ontbreekt:
Reshad F op 20/10/2013 18:02:48:
Code (php)
1
2
2
SELECT pers_achternaam, pers_voornaam, afdeling_naam
FROM zh_afdeling a, personeel p
FROM zh_afdeling a, personeel p
Back to topic:
Heel vaak ligt het optimaliseren meer in de manier waarop je de tabellen opzet, met andere woorden het juiste gebruik van indexen, dat wil zeggen zet indexen op kolommen waarop je joint (bij een FK gaat dat vanzelf) en op kolommen die je vrij consequent in de WHERE gebruikt.
Reshad F op 20/10/2013 18:21:01:
in dit geval haal ik alle gebruikers op waarvan de achternaam met een S begint of een letter na de S en vanuit de Radialogie afdeling komt.
Gebruik LIKE 'S%', in principe kan je geen >= gebruiken op een string, dat geeft niet altijd de juiste resultaten
Gewijzigd op 20/10/2013 18:44:29 door Ger van Steenderen
Ik snap dat opzet van tabellen een belangrijk factor is voor je optimalisatie maar de reden voor mijn vraag is specifiek voor de query zelf want ik heb binnenkort een tentamen over RDBMS en dan krijg je een kant en klaar tabel waar je de query, triggers, views etc zelf moet invullen en het optimaliseren van je query is er een van vandaar dat ik een beetje duidelijke richtlijnen voor mezelf wil maken aan de hand van welke is meest belangrijk en welke komt daarna.. ?
Klopt het overigens dat een zelfde query verschillende snelheden kan hebben als je deze achter elkaar uitvoert?
ik heb namelijk met 1 query resultaten als
13ms
20ms
21ms
17ms
etc
op je tweede antwoord
like 'S%' geeft me alleen de rijen die ook daadwerkelijk een S bevatten maar >= geeft ook alle letters erna dus
stuvwxyz
Gewijzigd op 20/10/2013 19:55:45 door Reshad F
Of je queries voldoende geoptimalisserd zijn te controleren kun je beter via EXLPAIN query, dan heb je meer beeld over wat er gebeurd.
Code (php)
1
2
3
4
5
2
3
4
5
SELECT p.pers_achternaam, p.pers_voornaam, a.afdeling_naam
FROM zh_afdeling AS a
LEFT JOIN personeel AS p ON (a.afdeling_nr = p.pers_afd_toegewezen)
AND p.pers_achternaam >= 'S'
AND a.afdeling_naam = 'Radiologie'
FROM zh_afdeling AS a
LEFT JOIN personeel AS p ON (a.afdeling_nr = p.pers_afd_toegewezen)
AND p.pers_achternaam >= 'S'
AND a.afdeling_naam = 'Radiologie'
Wat wellicht sneller is... omdat dat sneller dingen wegfiltert en zeker het proberen waard is:
Code (php)
1
2
3
4
5
2
3
4
5
SELECT p.pers_achternaam, p.pers_voornaam, a.afdeling_naam
FROM zh_afdeling AS a
LEFT JOIN personeel AS p ON (p.pers_afd_toegewezen = a.afdeling_nr)
AND a.afdeling_naam = 'Radiologie'
AND p.pers_achternaam >= 'S'
FROM zh_afdeling AS a
LEFT JOIN personeel AS p ON (p.pers_afd_toegewezen = a.afdeling_nr)
AND a.afdeling_naam = 'Radiologie'
AND p.pers_achternaam >= 'S'
Let ook op het omdraaien van p.pers_afd_toegewezen en a.afdeling_nr.
Daarnaast geef je wel aliassen (p en a) aan, maar gebruik je die niet. Doe dat (uiteraard) wel, zo hoeft SQL niet te gissen welke tabel je bedoelt.
Het maakt niks uit in welke volgorde je deze join voorwaarden zet.
Eddy E op 20/10/2013 18:17:27:
De query zoals in eerste instantie is getoond IS ook een join.Is een join niet sneller? Kan je een VIEW maken?
Deze methode is de 'oude' methode en heet impliciete join. Een impliciete join is even snel als de door jou bedoelde join. De join is in dit geval gespecificeerd in de WHERE clause:
Code (php)
1
2
3
4
5
2
3
4
5
SELECT pers_achternaam, pers_voornaam, afdeling_naam
FROM zh_afdeling a, personeel p
WHERE a.afdeling_nr = p.pers_afd_toegewezen
AND pers_achternaam >= 'S'
AND afdeling_naam = 'Radiologie'
FROM zh_afdeling a, personeel p
WHERE a.afdeling_nr = p.pers_afd_toegewezen
AND pers_achternaam >= 'S'
AND afdeling_naam = 'Radiologie'
Ger van Steenderen op 20/10/2013 18:37:56:
Gebruik LIKE 'S%', in principe kan je geen >= gebruiken op een string, dat geeft niet altijd de juiste resultaten
Reshad F op 20/10/2013 18:21:01:
in dit geval haal ik alle gebruikers op waarvan de achternaam met een S begint of een letter na de S en vanuit de Radialogie afdeling komt.
Gebruik LIKE 'S%', in principe kan je geen >= gebruiken op een string, dat geeft niet altijd de juiste resultaten
Merk wel op dat dat iets anders oplevert dan het voorbeeld. LIKE 'S%' geeft alles dat begint met een S, >= 'S' geeft ook de T en alles daarna.
Over het algemeen als je hebt over queries versnellen zijn er twee zaken van belang:
1) de juiste indexen hebben en die correct gebruiken
2) zo snel mogelijk je dataset verkleinen binnen de query
De eerste is over het algemeen het belangrijkst (geeft de meeste versnelling), maar de tweede moet je ook niet onderschatten. Zeker als je door middel van bepaalde berekeningen rijen wilt selecteren, dan is het zonder meer van belang om je dataset al te verkleinen voor je de berekening uitvoert. Ik heb hier zeer aanmerkelijke versnellingen mee gezien, die via simpele indexen niet te behalen zouden zijn.
Erwin H op 21/10/2013 16:57:37:
...de tweede moet je ook niet onderschatten. Zeker als je door middel van bepaalde berekeningen rijen wilt selecteren, dan is het zonder meer van belang om je dataset al te verkleinen voor je de berekening uitvoert. ...
Kan je een voorbeeld geven?
Iets groot, en wat je er van gemaakt hebt (of zo)
Als je echter eerst alleen de locaties selecteert die binnen een 10km bij 10km vierkant vallen waarvan de willekeurige plek het middelpunt is, dan verklein je je dataset aanzienlijk en die selectie kan wel via een index gebeuren. Dan doe je namelijk een simpele <= en => vergelijking. Vervolgens hoef je dan op een veel kleinere dataset de berekening uit te voeren.
Het voorbeeld dat je geeft, ben ik ook al tegengekomen.
Ik deed dit ook in twee stappen: eerst een vierkant gebied afbakenen. Dat rekent gemakkelijk, gewoon een min en max lng en lat.
en dan daarbinnen de berekening voor de cirkel.
Code (php)
1
.... WHERE delivery_date + INTERVAL 15 day >= CURRENT_DATE
Moet zijn:
Code (php)
1
.... WHERE delivery_date >= CURRENT_DATE - INTERVAL 15 day