[MySQL] efficientere matching dan joinen met aliassen

[AugmentoR]

Efficient matches vinden

Ik heb een tabel met 10.000 accounts (account_id, account_naam)
Ik heb een tabel met 300 fruitsoorten (fruit_Id, fruit_naam)
Ik heb hiertussen een koppeltabel met bijv. 100.000 ooitgegeten (account_id, fruit_id)

Nu wil ik een select maken (liefst natuurlijk met 1 query) dat bepaalt welke accounts een bepaalde combinatie van fruit hebben gegeten.

Een lijst met alle account-id's die ooit kiwi (fruit_id = 1) hebben gegeten is simpel
'SELECT account_id FROM ooitgegeten WHERE fruit_id = 1'
of, via een join
'SELECT a.account_id FROM account AS a INNER JOIN ooitgegeten AS o ON a.account_id = o.account_id WHERE o.fruit_id=1;'

Een lijst met alle account-id's die ooit kiwi én banaan (fruit_id=2) hebben gegeten doen we met twee joins:
'SELECT a.account_id FROM (account AS a INNER JOIN ooitgegeten AS o1 ON a.account_id = o1.account_id) INNER JOIN ooitgegeten AS o2 ON a.account_id = o2.account_id WHERE (((o1.fruit_id)=1) AND ((o2.fruit_id)=2));'

Een lijst met kiwi, banaan en appel kan worden opgehaald met drie joins:
'SELECT a.account_id FROM ((account AS a INNER JOIN ooitgegeten AS o1 ON a.account_id = o1.account_id) INNER JOIN ooitgegeten AS o2 ON a.account_id = o2.account_id) INNER JOIN ooitgegeten AS o3 ON a.account_id = o3.account_id WHERE (((o1.fruit_id)=1) AND ((o2.fruit_id)=2) AND ((o3.fruit_id)=3));'

Voor iedere parameter komt er dus een alias van de ooitgegeten-tabel bij. Het systeem werkt op zich prima, echter: het aantal parameters waarop in praktijk gefilterd gaat worden ligt minimaal rond de 50, en ik vraag me af of dit niet wat eleganter/efficienter kan dan met 50 joined alias-tabellen...

[Nick_S]

SQL:

SELECT account_id FROM ooitgegeten WHERE fruit_id in { 1, 2, 3} group by account_id having count(account_id) = 3

Zoiets?

[AugmentoR]

You the man, man!

[Confusion]

AugmentoR schreef op dinsdag 20 oktober 2009 @ 10:53:
Efficient matches vinden

Een minder ingewikkelde query is niet noodzakelijk efficienter. De query optimizer is meestal slimmer dan jij bent. Wat betreft efficientie mist het woord 'index' in je startpost

Verder heeft Nick_S me al het gras voor de voeten weggemaaid.

[Cartman!]

Zorgt de HAVING er niet voor dat er geen indexes gebruikt worden nu? Lijkt me dan weer niet zo heel efficient

[Nick_S]

Ik denk dat dat RDBMS afhankelijk is. Volgens mij zegt de SQL standaard niks over het gebruik van indexes. Je zou bij MySQL met EXPLAIN kunnen gaan kijken wat er gebruikt wordt.

[Confusion]

Cartman! schreef op dinsdag 20 oktober 2009 @ 11:49:
Zorgt de HAVING er niet voor dat er geen indexes gebruikt worden nu? Lijkt me dan weer niet zo heel efficient

Ik mag toch hopen dat hij gewoon indices gebruikt als er een having clause is...

[ Voor 9% gewijzigd door Confusion op 20-10-2009 12:00 ]

[Cartman!]

Wellicht ben ik dan in de war met iets anders

[cariolive23]

Nick_S schreef op dinsdag 20 oktober 2009 @ 10:58:

SQL:

SELECT account_id FROM ooitgegeten WHERE fruit_id in { 1, 2, 3} group by account_id having count(account_id) = 3

Zoiets?

Nee, deze query controleert ook of er bv. 3x fruit_id 1 voorkomt in de tabel. Er is dus geen enkele controle of de combinatie van 1, 2 én 3 voorkomt, alleen of een account exact 3x een fruit_id heeft geregistreerd. Wanneer iemand 4x iets heeft geregistreerd, bv. de combinatie 1,2,3 en 4, ook dan wordt er niets gevonden.

Je zult met MySQL 3x moeten JOINen en een AND moeten gebruiken, het is niet anders.

Met PostgreSQL kun je array_agg() gebruiken om dit probleem op te lossen, je hebt dan geen 10 join's nodig om een combinatie van 10 items te vinden.

[Kalentum]

Confusion schreef op dinsdag 20 oktober 2009 @ 11:59:
[...]

Ik mag toch hopen dat hij gewoon indices gebruikt als er een having clause is...

HAVING wordt op het allerlaatst toegepast, als de resultset al klaar is en voordat het resultaat naar de client wordt gestuurd. Er kan dan geen index meer gebruikt worden. Wil je indexen gebruiken, dan moet je WHERE gebruiken.

[Confusion]

rutgerw schreef op dinsdag 20 oktober 2009 @ 12:29:
[...]
HAVING wordt op het allerlaatst toegepast, als de resultset al klaar is en voordat het resultaat naar de client wordt gestuurd. Er kan dan geen index meer gebruikt worden. Wil je indexen gebruiken, dan moet je WHERE gebruiken.

Ik denk dat Cartman! bedoelde dat hij dan geen indices voor de join, where en group by meer gebruikte. Dat is misschien ooit zo geweest bij MySql, maar het lijkt me dat dat dan al een behoorlijke tijd niet meer zo is.

[Cartman!]

Dat bedoelde ik ja, het zou me niks verbazen als dat nog steeds het geval is echter.

[AugmentoR]

Allereerst @cariolive23 de group-by query werkt prima. In wezen selecteert hij alle records die aan één parameter voldoen, en daarnaa telt 'ie ze. Bij correct gebruik van keys (account_id, fruit_id unique) resulteert in een lange lijst waarvan de count(account_id) het aantal matches geeft. Die met count=aantal originele parameters houdt dus in dat er een 100% match is. Natuurlijk mag je dan 1 parameter niet vaker gebruiken.

Ik heb even e.e.a. gebenchmarkt met 700.000 ooitgegeten records met daarin 10.000 verschillende account_id's

De group-by methode:
bij 3 parameters: 0.3 sec
bij 12 parameters: 0.3 sec
bij 24 parameters: 0.4 sec
Indien er een limit (0,30) op zit gaat rap in 0.004 sec, indien limit (9000,30) dan zelfde tijd als de volle recordset.

de 'normale' één alias-per-parameter methode:
bij 3 parametes: 0.3 sec
bij 12 parameters: 0.5 sec
bij 24 parameters: 0.8 sec en een draak van een query
Inzetten van limits is bij beide zelfde verhaal: 0.30 gaat heel rap, en limit naar 9000 gaat op zelfde tijd als volle recordset.

Ik kies dan ook voor de group-by methode. Thanks allen!

[cariolive23]

Klopt, wanneer de combinatie (account_id, fruit_id) unique is, dan gaat het goed. Zonder de unique-constraint is de query fout. Alleen had je niet vermeld dat deze combinatie unique is (en unique moet zijn), daar mogen we dus ook niet zomaar aannames over gaan doen.

[B-Man]

cariolive23 schreef op woensdag 21 oktober 2009 @ 12:59:
Klopt, wanneer de combinatie (account_id, fruit_id) unique is, dan gaat het goed. Zonder de unique-constraint is de query fout. Alleen had je niet vermeld dat deze combinatie unique is (en unique moet zijn), daar mogen we dus ook niet zomaar aannames over gaan doen.

Al is redelijkerwijs aan te nemen dat deze tabel geen dubbele records bevat. Als er een kolom "gegeten op" o.i.d. had gestaan was het inderdaad vaag, omdat dat een sterke indicatie is dat een soort fruit meerdere malen gegeten is.

[winkbrace]

Cartman! schreef op dinsdag 20 oktober 2009 @ 11:49:
Zorgt de HAVING er niet voor dat er geen indexes gebruikt worden nu? Lijkt me dan weer niet zo heel efficient

Dus je wilt alle gegevens uit de tabel lezen en je vind dat 'ie dan over de index moet gaan ipv een full table scan. Huh?

[ACM]

BazzPsychoNut schreef op vrijdag 23 oktober 2009 @ 13:02:
Dus je wilt alle gegevens uit de tabel lezen en je vind dat 'ie dan over de index moet gaan ipv een full table scan. Huh?

Als je maar 2 kolommen nodig hebt kan dat uiteraard alsnog efficienter zijn, sowieso moet ie groeperen per auteurid, dus dan is het wel handig als ie die op volgorde uit kan lezen. Afgezien daarvan kent mysql de mogelijkheid om data uberhaupt niet uit de tabel te lezen als ie het ook al uit de index kan vinden. Dus een index 'fruit_id, auteur_id' kan dan best nuttig zijn.

Overigens is de group by variant simpel aan te passen om zonder unieke combinaties ook te werken... 'COUNT(DISTINCT fruitid) = 3' ipv gewoon COUNT.

[GlowMouse]

Heb je output van EXPLAIN? Dit moet zonder temporary table kunnen.