[MYSQL] Subquery voor elke rij

[Verwijderd]

Ik zit met het volgende, ik heb 2 tabellen : een met links en de andere met clicks. Gebruikers genereren links, welke mensen aan kunnen klikken, deze clicks worden in de tabel clicks gezet.

De clicks bevat enkele miljoenen rijen, en nu wil ik voor elke user het totale aantal clicks van al zijn links op vragen.

Nu had ik al een query : SELECT COUNT(*) FROM clicks WHERE link_id = ANY (SELECT id FROM `links` WHERE `user_id` = 1)
Enkel gaat deze al de miljoenen rijen af (duurt erg lang), dus wil ik het eingelijk andersom doen.
SELECT id FROM `links` WHERE `user_id` = 1 komen 20k aan rijen, en per rij wil ik dus een count van het aantal clicks per link, dit is veel sneller.

Als ik even heel vies met SELECT COUNT(*) FROM clicks WHERE link_id = .... alle 20k aan link_id's ... een query maak is deze dus zo klaar, alleen krijg ik het niet voor elkaar om dat netjes te doen.

Iemand een idee hoe ik dit voor elkaar krijg ?

[Megamind]

Misschien is een JOIN iets?

[Noork]

Waarom is een click een aparte regel? Is het zo dat je nog bepaalde gegevens van de klik bijhoudt? Anders zou je gewoon click steeds kunnen ophogen?

En idd, zoals Megamind aangeeft. Je zou even naar joins kunnen kijken. Hoewel ik me afvraag of het met miljoenen regels uberhaupt snel gaat worden.

[ Voor 33% gewijzigd door Noork op 28-05-2009 21:30 ]

[Verwijderd]

Noork schreef op donderdag 28 mei 2009 @ 21:29:
Waarom is een click een aparte regel? Is het zo dat je nog bepaalde gegevens van de klik bijhoudt? Anders zou je gewoon click steeds kunnen ophogen?

En idd, zoals Megamind aangeeft. Je zou even naar joins kunnen kijken. Hoewel ik me afvraag of het met miljoenen regels uberhaupt snel gaat worden.

Klopt inderdaad, er wordt aparte data bij gehouden, en een JOIN werkt inderdaad erg traag. Verder is het ophogen van de clicks wel iets wat moet gaan gebeuren, alleen op dit moment heb ik daar even niets aan, aangezien het in productie draait.

[ Voor 16% gewijzigd door Verwijderd op 28-05-2009 21:32 ]

[BCC]

Left join met een group by en een count moet toch echt wel aardig performen. Zit er ook een index op de click pagina?

[ Voor 21% gewijzigd door BCC op 28-05-2009 21:32 ]

[Megamind]

Verwijderd schreef op donderdag 28 mei 2009 @ 21:31:
[...]


Klopt inderdaad, er wordt aparte data bij gehouden, en een JOIN werkt inderdaad erg traag. Verder is het ophogen van de clicks wel iets wat moet gaan gebeuren, alleen op dit moment heb ik daar even niets aan, aangezien het in productie draait.

Mwa, met een paar goede indexen veronderstel ik dat een JOIN toch wel sneller zal moeten zijn dan een subquery...

[Verwijderd]

Megamind schreef op donderdag 28 mei 2009 @ 21:32:
[...]

Mwa, met een paar goede indexen veronderstel ik dat een JOIN toch wel sneller zal moeten zijn dan een subquery...

Niet wanneer er 100 miljoen rijen in staan .. dan werkt een join via die indexen ook niet echt lekker, en zoals ik al aangaf gaat dus een stuk sneller wanneer ik een hele rij met al die link_id's laat counten.

[P_de_B]

Weet je echt zeker dat de oplossing met een join traag is? Ik geloof er nl. niks van dat dat trager is dan een correlated subquery. Volgens mij optimaliseert MySQL dat ook niet naar een join. Zorg dat je indexen goed staan (op de velden van je join en je where) en probeer het nog eens zou ik zeggen.

[cariolive23]

EXPLAIN, dat hebben ze niet voor niets uitgevonden. Vervolgens ga je de juiste indexen aanmaken en nogmaals testen. Zorg er wel voor dat je niet de cache zit te testen, die gaat uiteraard snel genoeg maar je hebt er niets aan tijdens het testen.

Offtopic: 100 miljoen records in een MySQL-database, je bent een held. Zorg maar voor goede backups, MySQL heeft er (nog steeds) een handje van om de databestanden te corrumperen. Er bestaan betere/betrouwbaardere databases.

[Mike2k]

Ik vraag me af of je, als je 100 miljoen records hebt, je je database wel helemaal netjes hebt ingericht...

Afgezien van dat, waarom zou je aan je tabel Clicks niet het veld user_id en link_id toevoegen?
Dan kan je vanuit Clicks direct de link en user gegevens opvragen en je kan er ook direct op sorteren en zoeken..?

[BasieP]

Fastex schreef op donderdag 28 mei 2009 @ 22:17:
Ik vraag me af of je, als je 100 miljoen records hebt, je je database wel helemaal netjes hebt ingericht...

Afgezien van dat, waarom zou je aan je tabel Clicks niet het veld user_id en link_id toevoegen?
Dan kan je vanuit Clicks direct de link en user gegevens opvragen en je kan er ook direct op sorteren en zoeken..?

eensch, bij een tabel met zoveel records is redundantie tbv tijdswinst helemaal niet vreemd.
Zet dan direct even een index op de user_id aangezien je daar vast op wilt gaan zoeken

[BCC]

Inderdaad, en dan gewoon 1x per dag de nieuw gegenereerde clicks verwerken in de totalen.

[cariolive23]

100 miljoen records hoeft niet zo spannend te zijn, het is vooral interessant om te weten hoe groot de indexen zijn (in MB/GB's), of deze nog in het RAM passen en of ze wel (efficient) worden gebruikt door de database bij het uitvoeren van de queries. Zolang de indexen worden gebruikt en in RAM passen, kunnen de queries razendsnel zijn.

Mocht het niet meer in RAM passen, maak dan even een tabelletje met voorgekookte data en ga deze data met wat triggers onderhouden. Bij iedere wijziging in de grote tabel zal de trigger afgaan en vast de resultaten van wat berekeningen in jouw tabelletje gaan zetten. Een materialized view.

[P_de_B]

Met triggers een andere tabel onderhouden is niet echt een materialized view natuurlijk, volgens mij ondersteunt MySQL geen echte materialized views? Daarnaast is het effect van indexen bij MySQL minder - zeker in situaties waar er gejoined wordt en gefilterd omdat MySQL maar 1 index per tabel in een query gebruikt.

Ik ging er een beetje vanuit dat 100 miljoen meer genoemd was om 'grote tabellen' aan te duiden. Als er echt 100 miljoen records zijn is het punt iop zich wel goed. Als je veel data hebt is er niets op tegen op te denormaliseren.

[cariolive23]

volgens mij ondersteunt MySQL geen echte materialized views?

Klopt, maar het verschil tussen een echte en een niet echte materialized view hoeft niet zo groot te zijn. Dit verschil zal hem voornamelijk zitten in de eenvoud om hem aan te maken, of juist het gebrek daaraan.

Daarnaast is het effect van indexen bij MySQL minder

Tja, moet je maar een betere database nemen.

omdat MySQL maar 1 index per tabel in een query gebruikt.

MySQL is imho redelijk kut, maar inmiddels kan zelfs MySQL gebruik maken van meerdere indexen per tabel per query. Het is dan ook al 2009...

Als je veel data hebt is er niets op tegen op te denormaliseren.

Poe, gewaagde uitspraak. Ik zou zeker met veel data juist iets verder gaan normaliseren, ongenomaliseerde data wordt namelijk nog veel groter en dus worden jouw problemen ook veel groter. Met views of materialized views kun je de boel dan wel gaan vereenvoudigen/optimaliseren voor het gebruik in een applicatie, maar op databaseniveau hou ik het liever even netjes genormaliseerd. Genormaliseerde data kun je altijd denormaliseren met views of gaan partioneren, ongenormaliseerde data kun je zelden nog iets aan optimaliseren. Ik zou het niemand aanraden.

[FastWallie]

werkt de volgende query niet :

select link_id, count(*) from clicks
group by link_id

output op rustige tijden naar tabel pompen en indextje erop --> klaar

[P_de_B]

cariolive23 schreef op vrijdag 29 mei 2009 @ 00:07:
[...]
Klopt, maar het verschil tussen een echte en een niet echte materialized view hoeft niet zo groot te zijn. Dit verschil zal hem voornamelijk zitten in de eenvoud om hem aan te maken, of juist het gebrek daaraan.


[...]
Tja, moet je maar een betere database nemen.


[...]
MySQL is imho redelijk kut, maar inmiddels kan zelfs MySQL gebruik maken van meerdere indexen per tabel per query. Het is dan ook al 2009...

Hmm, misschien inmiddels wel. Het kon nooit in ieder geval

[...]
Poe, gewaagde uitspraak. Ik zou zeker met veel data juist iets verder gaan normaliseren, ongenomaliseerde data wordt namelijk nog veel groter en dus worden jouw problemen ook veel groter. Met views of materialized views kun je de boel dan wel gaan vereenvoudigen/optimaliseren voor het gebruik in een applicatie, maar op databaseniveau hou ik het liever even netjes genormaliseerd. Genormaliseerde data kun je altijd denormaliseren met views of gaan partioneren, ongenormaliseerde data kun je zelden nog iets aan optimaliseren. Ik zou het niemand aanraden.

Dat ben ik niet met je eens. Als je een gigantische hoeveelheid data hebt is er niets op tegen om te denormaliseren als dat een grote performancewinst oplevert. Een normale view, dus geen materialized, helpt je nog niet om sneller resulataat te krijgen als je dataset erg groot is. Je opmerking dat je ongenormaliseerde data niet kunt optimaliseren snap ik niet. Als je bijv. minder tabellen hoeft je joinen voor een specifieke query hebt je toch al iets geoptimaliseerd? Normaliseren is een goede zaak, maar geen wet van meden en perzen imo. Als je database beter performt door een aantal gegevens dubbel op te slaan, zou ik er niet van wakker liggen in ieder geval. Dat je nog verder wilt gaan normaliseren als je datahoeveelheid groeit snap ik al helemaal niet. In de meeste gevallen vererger je het performanceprobleem toch juist?

[XWB]

MySQL < 5.4 vertoont wel eens vaker vreemd gedrag bij subquerys. Waarom gebruik je geen JOIN en eventueel WHERE IN () ?

Poe, gewaagde uitspraak. Ik zou zeker met veel data juist iets verder gaan normaliseren, ongenomaliseerde data wordt namelijk nog veel groter en dus worden jouw problemen ook veel groter. Met views of materialized views kun je de boel dan wel gaan vereenvoudigen/optimaliseren voor het gebruik in een applicatie, maar op databaseniveau hou ik het liever even netjes genormaliseerd. Genormaliseerde data kun je altijd denormaliseren met views of gaan partioneren, ongenormaliseerde data kun je zelden nog iets aan optimaliseren. Ik zou het niemand aanraden.

Zelf al ga je maximaal normaliseren, dan nog kan het traag zijn. Dit echter zal altijd snel zijn:

SQL:

UPDATE links SET click = click+1 WHERE link_id = 1;
SELECT click FROM links l INNER JOIN user u ON l.user_id = u.user_id WHERE u.user_id = 3;

In sommige gevallen moet je afvragen of je voor de normen wil gaan - wat wel eens traag kan zijn - of je het gewoon snel wil.

[BCC]

Dit heeft IMHO niets met (de)normaliseren ten maken. Je bent (op een hele simpele manier) rapportages aan het genereren en cachen. Ik zou dus ook een clicks_report tabel maken met daarin een user_id, date en een click_total. Hieruit kun je dan mooi al je overviews genereren.

Is dit dubbele data? Volgens mij niet, want je kan het click_total niet op een andere manier opvragen (wel berekenen natuurlijk). Is het sneller? Ja. Clicks in het verleden kunnen toch niet meer veranderen.

[ Voor 6% gewijzigd door BCC op 30-05-2009 21:00 ]

[P_de_B]

Dit heeft wel met denormaliseren te maken. De 3NF eist dat er geen gegevens opgeslagen zijn die van andere gegevens af te leiden zijn. Een tabel vullen met totaaltellingen van clicks voldoet dus niet aan deze eis omdat het ook af te leiden is uit andere tabellen.

[BCC]

Prima, maar dat geeft alleen maar meer aan dat de 3NF in de praktijk nooit 100% houdbaar is. Het is een design guidline: een middel, geen doel. Door het dagelijks genereren van een rapportje is 1) het probleem weg en 2) kun je veel makkelijker rapportages van rapportages genereren.

[ Voor 40% gewijzigd door BCC op 31-05-2009 13:46 ]