Nu wil ik van de docenten KOR10 en KEH10 (dit zijn hun afkortingen die ook worden gebruikt in de kolom teacher) weten wanneer ze in week 22 tegelijk op school zijn. Met andere woorden: ik wil weten in week 22 wanneer zowel de week, day en hour bij de docenten gelijk zijn en ze dus tegelijk op school aanwezig zijn.
Ik heb van alles geprobeerd (met bijv. een self-join), maar ik krijg steeds rijen terug waarbij 1 docent les geeft op een moment dat de andere het niet doet.
SELECT DISTINCT a.week, a.day, a.hour, a.teacher, b.teacher
FROM lessons a
INNER JOIN lessons b ON (
a.week = b.week
AND a.day = b.day
AND a.hour = b.hour
AND a.teacher = 'KOR10'
AND b.teacher = 'KEH10'
)
WHERE a.week = 22
Dit zou bij een grote tabel alleen nog wel eens traag kunnen worden, omdat je voor elke student zo te zien een rij hebt en er dus vele combinaties mogelijk zijn, die hetzelfde resultaat opleveren. Als dat het geval is moet je even voor beide tabellen een subquery maken die alle dubbele eruit haalt, voor je de join maakt.
SELECT DISTINCT a.week, a.day, a.hour, a.teacher, b.teacher
FROM lessons a
INNER JOIN lessons b ON (
a.week = b.week
AND a.day = b.day
AND a.hour = b.hour
AND a.teacher = 'KOR10'
AND b.teacher = 'KEH10'
)
WHERE a.week = 22
Dit zou bij een grote tabel alleen nog wel eens traag kunnen worden, omdat je voor elke student zo te zien een rij hebt en er dus vele combinaties mogelijk zijn, die hetzelfde resultaat opleveren. Als dat het geval is moet je even voor beide tabellen een subquery maken die alle dubbele eruit haalt, voor je de join maakt.
Wauw super! Ik zat er heel dicht bij in de buurt. Ik was " a.week = b.week" vergeten, vandaar dat ik verkeerde resultaten kreeg. Heel erg bedankt! Nu even uitzoeken hoe ik dit subqueries kan doen, want het klopt inderdaad dat iedere student een eigen regel heeft.
Dit zou bij een grote tabel alleen nog wel eens traag kunnen worden, omdat je voor elke student zo te zien een rij hebt en er dus vele combinaties mogelijk zijn, die hetzelfde resultaat opleveren. Als dat het geval is moet je even voor beide tabellen een subquery maken die alle dubbele eruit haalt, voor je de join maakt.
Een subquerie is water naar de zee dragen in dit geval.
Dan krijg je immers ook een SELECT DISTINCT subquery ...
en je krijgt een tabel meer in de explain!
Zonder het ultieme antwoord te hebben, denk ik dat het in dit geval iets complexer ligt. Maar verbeter me waar mijn redenatie de mist ingaat.
Omdat er voor elke leerling een record in de tabel zit, zijn er bijvoorbeeld 10 records voor leraar a op tijdstip x en ook 10 records voor leraar b op tijdstip x. Dat betekent dus dat er 100 resultaten uit de join komen. Pas daarover wordt de distinct genomen.
Als je nu eerst de resultaten verkleint door in zowel eerste, als de tweede eerst te ontdubbelen, krijg je niet 100 resultaten, maar maar 1. Volgens mij zou dat in de meeste gevallen een snellere query kunnen opleveren. Ik bedoel dus dit:
SELECT a.week, a.day, a.hour, a.teacher, b.teacher
FROM (
SELECT DISTINT week, day, hour, teacher
FROM lessons
WHERE teacher = 'KOR10'
AND week = 22
) a
INNER JOIN (
SELECT DISTINT week, day, hour, teacher
FROM lessons
WHERE teacher = 'KEH10'
AND week = 22
) b ON (
a.week = b.week
AND a.day = b.day
AND a.hour = b.hour
)
Uiteraard heb ik dit niet getest (ik heb niet even zo'n gevulde tabel liggen) en is mijn ervaring met dit soort queries dat het soms het beste via trial and error te vinden is. Aan de andere kant is mijn ervaring ook dat het snel verkleinen van je dataset vaak hele grote versnellingen kan geven. Daarop is mijn suggestie gebaseerd.
Omdat er voor elke leerling een record in de tabel zit, zijn er bijvoorbeeld 10 records voor leraar a op tijdstip x en ook 10 records voor leraar b op tijdstip x. Dat betekent dus dat er 100 resultaten uit de join komen. Pas daarover wordt de distinct genomen.
Dit geldt dan ook voor de subqueries
Inderdaad kan je in een aantal gevallen de boel versnellen door met een subquery aan het begin de dataset te verkleinen.
Maar een subquery achter een inner join heeft meestal alleen maar een vertragend effect.
Je kan veel meer winnen door goed te indexen.
Dat is niet waar. Voor de subqueries geldt dat je twee keer 10 records hebt. Dat is 20, geen 100.
Ger van Steenderen op 22/05/2014 12:54:15
Inderdaad kan je in een aantal gevallen de boel versnellen door met een subquery aan het begin de dataset te verkleinen.
Maar een subquery achter een inner join heeft meestal alleen maar een vertragend effect.
Dat kan ik niet beoordelen en in dit geval is het dus alleen maar testen welke optie sneller is.
Ger van Steenderen op 22/05/2014 12:54:15
Je kan veel meer winnen door goed te indexen.
Niet altijd. Ja, goede indices helpen een heleboel queries te versnellen en zonder indices is het vaak een hopeloze zaak. Maar indices alleen zijn niet altijd genoeg en mijn ervaring is dat het verkleinen van je dataset in sommige situaties net zo goed kunnen helpen als een goed index dat in een andere situatie doet.
Ter verduidelijking:
Stel jouw voorbeeld:
de derived table levert tien resultaten op
de lessons in de subquery ook
hetzelfde geldt voor de tweede subquery
Nee Ger, dat is niet waar.
Maar om je eerlijk te zijn heb ik op dit moment geen zin om het uit te leggen omdat de oorspronkelijke vraag allang beantwoord is. Ik houd het hier dus bij.
Maar ik niet, want jij geeft mij ongelijk terwijl dat niet zo is:
Because type is ALL for each table, this output indicates that MySQL is generating a Cartesian product of all the tables; that is, every combination of rows. This takes quite a long time, because the product of the number of rows in each table must be examined. For the case at hand, this product is 74 × 2135 × 74 × 3872 = 45,268,558,720 rows. If the tables were bigger, you can only imagine how long it would take.
Eerste query, even zoder DISTINCT, met het resultaat:
SELECT a.week, a.day, a.hour, a.teacher, b.teacher
FROM lessons a
INNER JOIN lessons b ON (
a.week = b.week
AND a.day = b.day
AND a.hour = b.hour
AND a.teacher = 'KOR10'
AND b.teacher = 'KEH10'
)
WHERE a.week = 22
//resultaat:
22 1 8 KOR10 KEH10
22 1 8 KOR10 KEH10
22 1 8 KOR10 KEH10
22 1 8 KOR10 KEH10
22 1 8 KOR10 KEH10
22 1 8 KOR10 KEH10
22 1 8 KOR10 KEH10
22 1 8 KOR10 KEH10
22 1 8 KOR10 KEH10
Omdat je 3 rijen hebt voor KOR10 en drie rijen voor KEH10, wordt elke rij op elke rij gejoined en krijg je dus 9 rijen terug. Identieke rijen, dus na de distinct houd je er 1 over.
Tweede query:
SELECT a.week, a.day, a.hour, a.teacher, b.teacher
FROM (
SELECT DISTINT week, day, hour, teacher
FROM lessons
WHERE teacher = 'KOR10'
AND week = 22
) a
INNER JOIN (
SELECT DISTINT week, day, hour, teacher
FROM lessons
WHERE teacher = 'KEH10'
AND week = 22
) b ON (
a.week = b.week
AND a.day = b.day
AND a.hour = b.hour
)
Nu, uit de eerste subquery komt 1 rij terug:
SELECT DISTINT week, day, hour, teacher
FROM lessons
WHERE teacher = 'KOR10'
AND week = 22
//resultaat:
22 1 8 KOR10
Uit de tweede subquery komt ook 1 rij terug:
SELECT DISTINT week, day, hour, teacher
FROM lessons
WHERE teacher = 'KEH10'
AND week = 22
//resultaat:
22 1 8 KEH10
De join is dus maar 1 rij op 1 rij. Dat is dus een stuk kleinere tussen resultset dan een resultset van 9 rijen.
