[MYSQL] behouden van link/connectie

[Meijuh]

Ik heb een query die heel lang duurt, iets van 8 seconden, we hebben op meerdere manieren proberen te analyseren of deze sneller kan. Het resultaat is dat dit vrij onmogelijk is.
Nu heb ik dus een manier gevonden om een tijdelijke tabel aan te maken met mysql, namelijk met

code:

 CREATE TEMPORARY TABLE IF NOT EXISTS...

Nu duurt het verzamelen van data maar 1 keer 8 seconden en daarna enkele miliseconden. Dus allemaal prima, alleen wordt de tabel verwijderd als de verbinding sluit.
Ik wil eigenlijk de tabel nog bewaren, zolang ik nog met AJAX gegevens uit die tabel nodig heb.

Mijn vragen:
- wanneer sluit de verbinding? Nadat het phpscript is afgelopen? Of hoe lang daarna? (ik sluit hem niet met MiSQLi::close)
- kan ik op een of andere manier de verbinding openlaten, zodat deze nog te gebruiken is met ajax requests?
Misschien een identifier opslaan in een sessie of iets dergelijks?

[Gomez12]

Als php-script afgesloten ( dus pagina uitgeladen ) dan worden ook alle mysql verbindingen gesloten.

Heel erg vieze oplossing zou kunnen zijn om aan het einde van het php-script een infinite loop te maken met outputbuffering aan / flush die telkens een spatie extra dumpt onderaan de pagina met een sleep van 1 minuut erin ( en je php-execution time uiteraard op eeuwig zetten ) . Maar dit is zo ongeveer de meest smerige oplossing die ik kan bedenken. Is wel een heel leuke oplossing als je ooit nog eens een zelf-veroorzaakte DoS wilt hebben.

Ik zou gewoon geen temporary table aanmaken, maar de gegevens gewoon in een normale tabel opslaan, met een extra key ( sessionid bijv ) erbij om in deze tabel meerdere gebruikers gegevens te kunnen hebben staan, je moet dan enkel een extra ajax call hebben die deze tabel ( voor deze key ) weer leeghaalt als je klaar bent.

Http met php is gewoon 100% stateless. Elke call is nieuwe dbase-connectie etc.

[TeeDee]

Je zou ook eens kunnen kijken of de data altijd opgehaald moet worden. Zo niet, laat een cron job oid in de achtergrond de lange query uitvoeren, in een table zetten en deze queryen. M.a.w.: consolideer je data in een aparte set en gebruik die.

Eventueel kan je natuurlijk ook, mits de dataset niet te groot is, het e.e.a. gewoon in een sessie dumpen en die sessie gebruiken.

[Meijuh]

Gomez12 schreef op woensdag 16 september 2009 @ 21:27:

Ik zou gewoon geen temporary table aanmaken, maar de gegevens gewoon in een normale tabel opslaan, met een extra key ( sessionid bijv ) erbij om in deze tabel meerdere gebruikers gegevens te kunnen hebben staan, je moet dan enkel een extra ajax call hebben die deze tabel ( voor deze key ) weer leeghaalt als je klaar bent.

Hoe roep ik die extra ajax call dan aan, stel dat iemand eerder de browser sluit bijvoorbeeld?
En deze oplossing zorgt er ook voor dat de tabel zichtbaar is voor elke connectie, dus moet elke tabel een aparte naam hebben en dit wordt vrij vies...

[Meijuh]

TeeDee schreef op woensdag 16 september 2009 @ 21:28:
Je zou ook eens kunnen kijken of de data altijd opgehaald moet worden. Zo niet, laat een cron job oid in de achtergrond de lange query uitvoeren, in een table zetten en deze queryen. M.a.w.: consolideer je data in een aparte set en gebruik die.

Eventueel kan je natuurlijk ook, mits de dataset niet te groot is, het e.e.a. gewoon in een sessie dumpen en die sessie gebruiken.

De temptable kan op heeel veer verschillende manieren samengesteld worden >1000 manieren.
Dus dan zou de cronjob voor al deze mogelijke verzamelingen een tabel moeten maken, lijkt me ook geen oplossing.

[pedorus]

Meijuh schreef op woensdag 16 september 2009 @ 21:14:
Ik heb een query die heel lang duurt, iets van 8 seconden, we hebben op meerdere manieren proberen te analyseren of deze sneller kan. Het resultaat is dat dit vrij onmogelijk is.

Lijkt me eigenlijk sterk. Hier ligt in ieder geval het probleem, de rest is symptoombestrijding.

Nu heb ik dus een manier gevonden om een tijdelijke tabel aan te maken met mysql, namelijk met

code:

 CREATE TEMPORARY TABLE IF NOT EXISTS...

Nu duurt het verzamelen van data maar 1 keer 8 seconden en daarna enkele miliseconden. Dus allemaal prima, alleen wordt de tabel verwijderd als de verbinding sluit.
Ik wil eigenlijk de tabel nog bewaren, zolang ik nog met AJAX gegevens uit die tabel nodig heb.

Dat is potentieel jaaaren later... Lijkt me niet.

[CodeCaster]

Wat doet die query dan? Waarom sla je het resultaat ervan niet op?

[HuHu]

Kun je geen persistent connection gebruiken?

[BasieP]

mmm
het lijkt er op alsof je iets meer info moet geven.. korte samenvatting kom ik op dit:

- je hebt een lange query die je wilt uitvoeren, en vervolgens het resultaat een x aantal keer bevragen.
- echter kan je het resultaat niet opslaan want de query kan op 1000+ manieren uitgevoert worden (dus 1000+ verschillende resultaten hebben)

klinkt als:
- ik wil een tijdelijke tabel
- maar ik heb elke keer verschillende data in die tabel..

make up your mind.
een query van 8 seconden wil je niet per hit doen. behalve dat het lang duurt gaat je DB dat niet trekken bij meer hits.

ik zou eens kijken of je in tijdelijke tabellen (meerdere) dmv een cronjob info kan opslaan waaruit je die 1000+ resultaten kunt halen binnen 0.0x seconden.


PS.
je zou ons gewoon kunnen vertellen wat voor systeem je aan't bouwen ben, dat maakt praten een stuk makkelijker

[TeeDee]

Meijuh schreef op woensdag 16 september 2009 @ 21:34:
[...]
De temptable kan op heeel veer verschillende manieren samengesteld worden >1000 manieren.
Dus dan zou de cronjob voor al deze mogelijke verzamelingen een tabel moeten maken, lijkt me ook geen oplossing.

Kijk waar nodig of je andere data niet kan consolideren. Het opslaan van Postcounts in een Forum bijvoorbeeld scheelt behoorlijk wat impact op de running data. Als je veel aggregate functies gebruikt bijvoorbeeld. Liggen de indexen goed? Is het systeem waar de DB op staat wel snel genoeg?

[ Voor 4% gewijzigd door TeeDee op 16-09-2009 21:42 ]

[Meijuh]

HuHu schreef op woensdag 16 september 2009 @ 21:37:
Kun je geen persistent connection gebruiken?

Hmm ik lees niet veel goede dingen over een persistant connection, maar het lijkt wel een oplossing.
Hoe adviseer jij om uiteindelijk toch deze verbinding te sluiten?

[HuHu]

Meijuh schreef op woensdag 16 september 2009 @ 21:44:
[...]


Hmm ik lees niet veel goede dingen over een persistant connection, maar het lijkt wel een oplossing.
Hoe adviseer jij om uiteindelijk toch deze verbinding te sluiten?

Nou... niet dus. Vandaar persistent hè. Alleen vanaf de MySQL server kun je ze killen denk ik. Lees dit ff: http://nl3.php.net/manual...ersistent-connections.php

Verder kun je ook gewoon een cache implementeren. Zend_Cache of APC bijvoorbeeld en dan kieper je die tabel daarin.

[cariolive23]

Ik heb een query die heel lang duurt, iets van 8 seconden, we hebben op meerdere manieren proberen te analyseren of deze sneller kan. Het resultaat is dat dit vrij onmogelijk is.

En wat zegt EXPLAIN van jouw query? En hoe zien de betrokken tabellen en indexen eruit? Maakt de query al wel optimaal gebruik van indexen? Zo niet, dan kan de query waarschijnlijk nog wel veel sneller draaien, een factor 1000 is geen uitzondering.

[Meijuh]

Hoe kan ik makkelijk een DESCRIBE tabelnaam geven die ik hier kan posten?

[TeeDee]

que? gewoon

code:

mysql> DESCRIBE tabelnaam;

uitvoeren?

[Gomez12]

Meijuh schreef op woensdag 16 september 2009 @ 21:32:
[...]


Hoe roep ik die extra ajax call dan aan, stel dat iemand eerder de browser sluit bijvoorbeeld?
En deze oplossing zorgt er ook voor dat de tabel zichtbaar is voor elke connectie, dus moet elke tabel een aparte naam hebben en dit wordt vrij vies...

Simpele vraag : wat wil je nou? Je wilt een magische tabel hebben die bestaat zolang je ajax ernaartoe wilt kunnen sturen, maar die verdwenen is als je er geen ajax meer naar toe wilt sturen en je hebt daarvoor een taal / platform gekozen wat geen state kent...

Als iemand de browser sluit moet je periodiek een systeem hebben wat je tabel leegt.
Elke tabel een aparte naam wordt vies? Enig idee wat temporary tables zijn?

De tabel hoeft trouwens helemaal niet zichtbaar te zijn voor elke connectie, gewoon enkel insert rechten geven op de tabel en dan een view per gebruiker maken.

Als ik jou was zou ik toch echt maar eens de documentatie goed doorlezen, want zo ongeveer alle bezwaren die jij hebt heb je ook met temporary tables, alleen zie je die niet omdat ze te kort bewaard worden... Jij wilt ze langer bewaren en dan vind je je eigen gebruikte oplossing vies...

Meijuh schreef op woensdag 16 september 2009 @ 21:34:
[...]
De temptable kan op heeel veer verschillende manieren samengesteld worden >1000 manieren.
Dus dan zou de cronjob voor al deze mogelijke verzamelingen een tabel moeten maken, lijkt me ook geen oplossing.

En alle >1000 manieren leveren uiteraard een wachttijd op van 8 sec?
Creeer gewoon 1 ( desnoods 2 of 3 ) algemene tabel in een cronjob en vuur daar je >1000 query's op uit. Het zal misschien vanwege het generiek zijn van de tabel niet altijd in 0,1 milliseconde klaar zijn, maar het zal altijd een gigantische verbetering opleveren tov 8 sec.


Sowieso lijkt het me raar dat je 1 query op alle mogelijke manieren hebt geanalyseerd en daarna kom je met nog > 999 andere query's aanzetten waardoor het opeens onmogelijk wordt. KIES WAT JE WIL!!!

[Meijuh]

TeeDee schreef op woensdag 16 september 2009 @ 22:06:
que? gewoon

code:

mysql> DESCRIBE tabelnaam;

uitvoeren?

Ja I know maar dan krijg ik geen mooie uitgelijnde tekst die ik hier kan plaatsen.

Maar dit is de uitkomst van de explain:



ik zal zo ff de structuur van de tabellen plaatsen

Ik heb nog nooit een explain gebruikt, wat zegt dit precies?

[ Voor 7% gewijzigd door Meijuh op 16-09-2009 22:12 ]

[CodeCaster]

Ik zie een temporary table met 12000 rows. Volgens mij is dat niet helemaal de bedoeling

[cariolive23]

Over zo'n simpele query met slechts 12k records, doet jouw database 8 seconden?

Ik zou de JOIN's als JOIN uitschrijven, de WHERE ook naar een JOIN verplaatsen en de GROUP BY compleet maken. Veel verschil mag het niet maken, het is een query van niets. Ik zie niet waarom dit zolang zou moeten duren. Of gebruik je hardware van 15 jaar oud? Of heb je nog 100 andere queries op je databaseserver draaien?

[Meijuh]

cariolive23 schreef op woensdag 16 september 2009 @ 22:18:
Over zo'n simpele query met slechts 12k records, doet jouw database 8 seconden?

Ik zou de JOIN's als JOIN uitschrijven, de WHERE ook naar een JOIN verplaatsen en de GROUP BY compleet maken. Veel verschil mag het niet maken, het is een query van niets. Ik zie niet waarom dit zolang zou moeten duren. Of gebruik je hardware van 15 jaar oud? Of heb je nog 100 andere queries op je databaseserver draaien?

Ga ik proberen het is een vrij snelle server met vaak 1 connectie, dus het zou heel snel moeten gaan.

Maar toch bevat: eigenschap 88 rijen, respondent 59000 rijen en respondent_has_eigenschap 450000 rijen

[TeeDee]

Meijuh schreef op woensdag 16 september 2009 @ 22:22:
[...]
Ga ik proberen het is een vrij snelle server met vaak 1 connectie, dus het zou heel snel moeten gaan.

Geef nou eens specifieke informatie...

Neem ook de JOIN tips van cariolive23 ter harte.

[Gomez12]

Meijuh schreef op woensdag 16 september 2009 @ 22:22:
[...]
Ga ik proberen het is een vrij snelle server met vaak 1 connectie, dus het zou heel snel moeten gaan.

Maar toch bevat: eigenschap 88 rijen, respondent 59000 rijen en respondent_has_eigenschap 450000 rijen

Heb je wel de juiste query geexplained? Want met deze aantallen zou enkel fulltable scans al binnen 1 sec klaar moeten zijn volgens mij ( op een degelijke server ).

Hier worden nog indexen gebruikt ook. Of je hebt echt gigagigantisch ( herhaling is bewust ) grote blobs / textvelden in meerdere tabellen zitten of je hebt volgens mij echt de verkeerde query ge-explained.

Sowieso returned dit maar 1 resultaat, waar zijn de >1000 andere?

[Meijuh]

TeeDee schreef op woensdag 16 september 2009 @ 22:28:
[...]

Geef nou eens specifieke informatie...

Het zal ongeveer een pentium 4 zijn op 2,6 GHz, hij staat bij mijn collega thuis, dus ik weet het niet precies.
Als ik de GROUP BY weglaat in de query kan hij wel worden uitgevoerd in +/- 0.0005 seconden.

Neem ook de JOIN tips van cariolive23 ter harte.

Ik heb nu deze query:

code:

SELECT e.id, e.eigenschap_type FROM eigenschap e JOIN respondent_has_eigenschap re ON (re.eigenschap_id = e.id)
JOIN respondent r ON (r.id = re.respondent_id AND r.panel_id = 35) GROUP BY e.id, e.eigenschap_type

Ik heb die WHERE conditie verplaatst, klopt dat zo (geeft dit het zelfde resultaat)?
En hoe moet ik die GROUP BY verder uitschrijven, moet ik bijvoorbeeld ook groeperen op re.eigenschap_id? Op welke moet ik nog meer grouperen?
Overigens duurt deze query ongeveer 4 seconden. (Als ik de GROUP BY weglaat heel kort (milliseconden)).

[Meijuh]

Gomez12 schreef op woensdag 16 september 2009 @ 22:40:
[...]

Heb je wel de juiste query geexplained? Want met deze aantallen zou enkel fulltable scans al binnen 1 sec klaar moeten zijn volgens mij ( op een degelijke server ).

Hier worden nog indexen gebruikt ook. Of je hebt echt gigagigantisch ( herhaling is bewust ) grote blobs / textvelden in meerdere tabellen zitten of je hebt volgens mij echt de verkeerde query ge-explained.

Sowieso returned dit maar 1 resultaat, waar zijn de >1000 andere?

Als ik panel_id = 35 vervang door een andere identifier.

edit: ik heb echt de goede geexplained, wel raar, maar ik kan er niks anders van maken..

[ Voor 6% gewijzigd door Meijuh op 16-09-2009 22:45 ]

[cariolive23]

De query die je nu hebt, is anders dan het origineel. Je laat de COUNT() weg en dus kan ook de GROUP BY worden weggelaten, die slaat dan ook nergens meer op.

Maar schijf je queries voortaan even wat netter uit, dan worden ze ineens leesbaar:

SQL:

SELECT
    e.id,
    e.eigenschap_type,
    COUNT(e.id) AS aantal
FROM
    respondent_has_eigenschap AS re
        JOIN respondent AS r ON (re.respondent_id = r.id AND r.panel_id = 35)
        JOIN eigenschap AS e ON re.eigenschap_id = e.id
GROUP BY
    e.id, 
    e.eigenschap_type
ORDER BY
    aantal DESC;

(niet getest, nog met de COUNT en dus ook de GROUP BY)

De COUNT zal de boel langzaam maken, zeker omdat je later op dit resultaat gaat sorteren. Dat kan niet met een index, deze is er niet. Alle kolommen die in de SELECT staan maar NIET in een aggregate functie, MOETEN in de GROUP BY staan.

Een index op de combinatie van e.id en e.eigenschap_type kan de boel wellicht versnellen. Dat zul je even moeten uitproberen.

[pedorus]

Meijuh schreef op woensdag 16 september 2009 @ 22:41:

code:

SELECT e.id, e.eigenschap_type FROM eigenschap e JOIN respondent_has_eigenschap re ON (re.eigenschap_id = e.id)
JOIN respondent r ON (r.id = re.respondent_id AND r.panel_id = 35) GROUP BY e.id, e.eigenschap_type

Je had eerst:

SQL:

explain select e.id, e.eigenschap_type, count(e.id) as aantal 
    from eigenschap e, respondent_has_eigenschap re, respondent r 
    where r.panel_id=35 and e.id=re.eigenschap_id and re.respondent_id=r.id 
    group by e.id order by aantal desc

Wat krijg je als je dat herschrijft?

SQL:

explain select e.id, e.eigenschap_type, count(*) as aantal 
    from respondent r inner join respondent_has_eigenschap re 
         on re.respondent_id=r.id inner join eigenschap e on e.id=re.eigenschap_id 
    where r.panel_id=35 group by e.id order by aantal desc

[Gomez12]

Meijuh schreef op woensdag 16 september 2009 @ 22:42:
[...]
Als ik panel_id = 35 vervang door een andere identifier.

edit: ik heb echt de goede geexplained, wel raar, maar ik kan er niks anders van maken..

Dat zijn toch geen >1000 varianten, dat is gewoon in een crontable een tabel maken met :
e.id,e.eigenschap_type,r.panel_id ( query mag je zelf bedenken ) en dan een index op r.panel_id. Maar dit moet echt niet nodig zijn met deze aantallen...

Je group by is in je laatste query goed, deze geeft niet het aantal als resultaat terug ( simpelweg je count weer toevoegen ). Ik gok uit mijn hoofd dat een index over e.id en e.eigenschap_type ( in die volgorde ) wel versnelling zal brengen, je kan dan de index voor e.id verwijderen ( valt al binnen de gecombineerde index en mysql gebruikt afaik nog steeds enkel 1 index per tabel )

En wat hierboven al gezegd wordt, post je query's op een duidelijke manier en zorg ervoor dat ze gelijk zijn... Die order by op een count had ik bijv even gemist ( staat niet in je laatste query ), die vertraagt je query erg.

[ Voor 11% gewijzigd door Gomez12 op 16-09-2009 23:06 ]

[cariolive23]

Hoe is jouw server geconfigureerd? Hoeveel RAM is er tot jouw beschikking? Wanneer de resultset niet in RAM past en naar disk moet worden weggeschreven (swap), dan wordt de boel onvoorstelbaar langzaam. Dat past goed bij jouw huidige situatie, een hele langzamen query terwijl je eigenlijk niets bijzonders doet.

Ga eens naar de configuratie kijken en zoek eens uit wat je in MySQL kunt aanpassen. Er zijn ook mogelijkheden om dit runtime te doen voor jouw ene connectie, je hebt niet direct toegang tot de configuratiebestanden nodig. Zie ook de MySQL-handleiding, ik heb deze kennis niet meer paraat, MySQL en ik hebben afscheid van elkaar genomen...

[Meijuh]

pedorus schreef op woensdag 16 september 2009 @ 22:51:
[...]

Je had eerst:

SQL:

explain select e.id, e.eigenschap_type, count(e.id) as aantal 
    from eigenschap e, respondent_has_eigenschap re, respondent r 
    where r.panel_id=35 and e.id=re.eigenschap_id and re.respondent_id=r.id 
    group by e.id order by aantal desc

Wat krijg je als je dat herschrijft?

SQL:

explain select e.id, e.eigenschap_type, count(*) as aantal 
    from respondent r inner join respondent_has_eigenschap re 
         on re.respondent_id=r.id inner join eigenschap e on e.id=re.eigenschap_id 
    where r.panel_id=35 group by e.id order by aantal desc

Ik dacht eerst dat die count() niet veel uitmaakte, maargoed.
Ik heb die index toegevoegd op de combinatie van e.id en e.eigenschap_type, maar de query duurt nog steeds 8 seconden, ik gebruik:

SQL:

explain select e.id, e.eigenschap_type, count(*) as aantal 
    from respondent r inner join respondent_has_eigenschap re 
         on re.respondent_id=r.id inner join eigenschap e on e.id=re.eigenschap_id 
    where r.panel_id=35 group by e.id, e.eigenschap_type order by aantal desc

als query

verder is het zo dat respondent_has_eigenschap.eigenschap_waarde is een text veld en eigenschap.eigenschap_type is een text-veld. Maakt dit nog uit?

[Meijuh]

cariolive23 schreef op woensdag 16 september 2009 @ 23:12:
Hoe is jouw server geconfigureerd? Hoeveel RAM is er tot jouw beschikking? Wanneer de resultset niet in RAM past en naar disk moet worden weggeschreven (swap), dan wordt de boel onvoorstelbaar langzaam. Dat past goed bij jouw huidige situatie, een hele langzamen query terwijl je eigenlijk niets bijzonders doet.

Ga eens naar de configuratie kijken en zoek eens uit wat je in MySQL kunt aanpassen. Er zijn ook mogelijkheden om dit runtime te doen voor jouw ene connectie, je hebt niet direct toegang tot de configuratiebestanden nodig. Zie ook de MySQL-handleiding, ik heb deze kennis niet meer paraat, MySQL en ik hebben afscheid van elkaar genomen...

Ik heb de query ook even gedraait op de productie server (een server die staat bij byte.nl). Daar duurt die ook gewoon 8 seconden, het gaat hier om ongeveer evenveel rijen die in de tabellen staan.

edit: ik heb de count weggelaten en de GROUP BY vervangen door een DISTINCT en dit gaat gewoon in 0.0027 seconden, maar nu ben ik wel mijn count kwijt, das wel lastig, omdat sommige eigenschappen niet interessant zijn.

Waarom gaat dit met een DISTINCT wel snel? Ik dacht dat ik altijd moest oppassen met het gebruik van een distinct, en zo waar mogelijk distinct vervangen door group by?

[ Voor 16% gewijzigd door Meijuh op 16-09-2009 23:32 ]

[Gomez12]

Meijuh schreef op woensdag 16 september 2009 @ 23:18:
[...]
Ik dacht eerst dat die count() niet veel uitmaakte, maargoed.
Ik heb die index toegevoegd op de combinatie van e.id en e.eigenschap_type, maar de query duurt nog steeds 8 seconden, ik gebruik:

SQL:

explain select e.id, e.eigenschap_type, count(*) as aantal 
    from respondent r inner join respondent_has_eigenschap re 
         on re.respondent_id=r.id inner join eigenschap e on e.id=re.eigenschap_id 
    where r.panel_id=35 group by e.id, e.eigenschap_type order by aantal desc

als query

verder is het zo dat respondent_has_eigenschap.eigenschap_waarde is een text veld en eigenschap.eigenschap_type is een text-veld. Maakt dit nog uit?

Waarom is eigenschap_type een text veld?

Die zou het enigszins kunnen vertragen, testquery die ik even uit de losse mouw schud :

code:

select e.id, e.eigenschap_type, count(*) as aantal 
from eigenschap
inner join (select e.id
    from respondent r inner join respondent_has_eigenschap re 
         on re.respondent_id=r.id inner join eigenschap e on e.id=re.eigenschap_id 
    where r.panel_id=35 group by e.id) as tmp on (tmp.id=e.id)
order by aantal desc

even aangenomen dat al je id-velden ints ( en keys ) zijn zou dit volgens mij iets sneller moeten gaan.
Je textvelden komen nu niet in je tussenresultaten ( want die zijn groot ) enkel in je eindresultaat.

Wat betreft distinct / group by. Het hangt af van wat er voor aggegrated functions gebruikt worden welke sneller is, ik dacht dat group by wel gebruik kon maken van indexen, distinct weer niet.
Distinct kan gewoon alle dubbele weggooien waar group by ze met sommige aggegrated functions nog nodig heeft ( bijv om te tellen )
Maar sowieso is het niet het een of het ander, het zijn gewoon verschillende functies met andere doelen / gebruiken. Een group by hier zonder count is nutteloos, een distinct levert weer geen count op...

[ Voor 15% gewijzigd door Gomez12 op 16-09-2009 23:40 ]

[Meijuh]

Waarom is eigenschap_type een text veld?

In principe hoeft dit ook niet, ik heb het zelf niet bedacht, maar geen enkele waarde in deze kolom is groter dan 255 tekens, dus... zou het veel schelen als ik er een varchar van maak?

even aangenomen dat al je id-velden ints ( en keys ) zijn zou dit volgens mij iets sneller moeten gaan.
Je textvelden komen nu niet in je tussenresultaten ( want die zijn groot ) enkel in je eindresultaat.

Het gaat inderdaad iets sneller, maar ik vind het toch nog raar dat de query 1000x te langzaam is ten opzichte van hoe snel die eigenlijk moet zijn (als ik het vergelijk met de distinct-versie).

[pedorus]

Meijuh schreef op woensdag 16 september 2009 @ 23:18:

SQL:

explain select e.id, e.eigenschap_type, count(*) as aantal 
    from respondent r inner join respondent_has_eigenschap re 
         on re.respondent_id=r.id inner join eigenschap e on e.id=re.eigenschap_id 
    where r.panel_id=35 group by e.id, e.eigenschap_type order by aantal desc

e.eigenschap_type hoeft - zeker voor testen - niet in de group by aangezien er een functionele afhankelijkheid eigenschap.id->eigenschap.eigenschap_type is. Het vertraagd alleen maar (zie voor verdere discussie oa hier). Daarnaast: heeft respondent een key op panel_id? Wat is de output van deze explain?

Dat het zonder count met een distinct wel snel gaat komt waarschijnlijk doordat er niet zoveel eigenschappen gechecked hoeven te worden (eerste hit is klaar), en er keys beschikbaar voor zijn. Zonder data, explains of iets anders valt er weinig over te zeggen..

[Meijuh]

pedorus schreef op woensdag 16 september 2009 @ 23:49:
[...]

e.eigenschap_type hoeft - zeker voor testen - niet in de group by aangezien er een functionele afhankelijkheid eigenschap.id->eigenschap.eigenschap_type is. Het vertraagd alleen maar (zie voor verdere discussie oa hier). Daarnaast: heeft respondent een key op panel_id? Wat is de output van deze explain?

respondent heeft een index op panel_id

De output:

Dat het zonder count met een distinct wel snel gaat komt waarschijnlijk doordat er niet zoveel eigenschappen gechecked hoeven te worden (eerste hit is klaar), en er keys beschikbaar voor zijn. Zonder data, explains of iets anders valt er weinig over te zeggen..

Zou het sneller gaan wanneer ik een nieuwe tabel ga bijhouden waarin panel_id en eigenschap_id wordt bijgehouden? Lijkt me niet toch, want daar zijn die indices voor?

[curry684]

pedorus schreef op woensdag 16 september 2009 @ 23:49:
[...]

Het vertraagd alleen maar

offtopic:
Heb je daar eigenlijk serieuze onderbouwing in de vorm van benchmarks oid voor?

[GlowMouse]

Een text-veld is het slechtste wat je kunt hebben. Pak dan varchar (zie limitaties memory engine).

Zelf zou ik hem denk ik zo doen, met een index op (e.id,e.eigenschap_type), op (r.panel_id) en op (re.respondent_id).

SQL:

SELECT
    e.id,
    e.eigenschap_type,
    COUNT(e.id) AS aantal
FROM
  eigenschap AS e
  JOIN
   (SELECT eigenschap_id
    FROM respondent r
    JOIN respondent_has_eigenschap AS re ON (re.respondent_id = r.id)
    WHERE r.panel_id = 35) AS tmp ON tmp.eigenschap_id = e.id 
GROUP BY
    e.id, 
    e.eigenschap_type
ORDER BY
    aantal DESC;

En ik mis nog steeds een duidelijk overzicht van je huidige indices. Zonder uitleg kan ik weinig met je explain output.

[ Voor 9% gewijzigd door GlowMouse op 17-09-2009 00:11 ]

[Gomez12]

Meijuh schreef op woensdag 16 september 2009 @ 23:48:
[...]
In principe hoeft dit ook niet, ik heb het zelf niet bedacht, maar geen enkele waarde in deze kolom is groter dan 255 tekens, dus... zou het veel schelen als ik er een varchar van maak?

Met mijn laatste query zou het volgens mij niet meer moeten uitmaken, er komt uit de subquery maar 1 resultaat, dus hoeft er maar 1 text-veld opgehaald te worden, dit neemt maar 4k in beslag.

Bij iets beter nadenken is er een kans dat door die count mysql alsnog gedwongen wordt om meerdere text-velden op te halen / door te lopen wat al heel snel heel veel geheugen in gaat nemen.

2e poging :

code:

select eigenschap.id, eigenschap.eigenschap_type, tmp.aantal
from eigenschap
inner join (select e.id, count(*) as aantal
    from eigenschap as e 
         inner join respondent_has_eigenschap as re on e.id=re.eigenschap_id
         inner join respondent as r on (r.panel_id=35 and re.respondent_id=r.id)
    group by e.id) as tmp on (tmp.id= eigenschap.id)
order by aantal desc

De count even naar binnen verplaatst en de join volgorde iets aangepast naar iets wat volgen mij makkelijker door de profiler te doen is en wat mogelijke misverstanden van de profiler uit de weg geruimd door 1 dubbele alias eruit te halen.

Als het innodb is dan zou hij nog ietsie sneller kunnen door ipv count(*) count(e.id) te gebruiken, maar als het myisam tabellen zijn dan is de huidige opzet weer sneller.

Wat is trouwens de execution time van de inner query ( want die zou gewoon 0.01 moeten zijn oid als alles ints en keys zijn )

[...]
Het gaat inderdaad iets sneller, maar ik vind het toch nog raar dat de query 1000x te langzaam is ten opzichte van hoe snel die eigenlijk moet zijn (als ik het vergelijk met de distinct-versie).

Dat is net zoiets als zeggen dat je het raar vind dat een vliegtuig tig x zo snel gaat als een wandelaar, je distinct / group by varianten leveren niet hetzelfde resultaat, doorlopen niet hetzelfde execution path etc. en zijn dus niet gelijk, dus niet even snel.

[GlowMouse]

Gomez12 schreef op donderdag 17 september 2009 @ 00:11:
[...]

Als het innodb is dan zou hij nog ietsie sneller kunnen door ipv count(*) count(e.id) te gebruiken, maar als het myisam tabellen zijn dan is de huidige opzet weer sneller.

BS, count(*) is altijd minstens zo snel. Vermelden van een kolomnaam heeft alleen zin bij een kolom die NULL kan zijn.

[pedorus]

Meijuh schreef op woensdag 16 september 2009 @ 23:58:
respondent heeft een index op panel_id

En dat is respondent_FKindex1? Heeft respondent 12194 rijen, of zijn er 12194 rijen met panel_id=35? Hoeveel rijen hebben die 3 tabellen ongeveer (per tabel)? Wat voor aantallen worden er ongeveer geretourneerd na de group by?

De output: [afbeelding]

Gooi die tekst (eigenschap_type) uit de group by. Het vertraagt volgens dit artikel, en zonder vertaagt het in ieder geval niet.

Zou het sneller gaan wanneer ik een nieuwe tabel ga bijhouden waarin panel_id en eigenschap_id wordt bijgehouden? Lijkt me niet toch, want daar zijn die indices voor?

Hoeveel rijen zou een ongenormaliseerde versie van alle data in de drie tabellen zijn?

SQL:

select count(*) from respondent r inner join respondent_has_eigenschap re 
         on re.respondent_id=r.id inner join eigenschap e on e.id=re.eigenschap_id

[Gomez12]

pedorus schreef op woensdag 16 september 2009 @ 23:49:
[...]
Het vertraagd alleen maar (zie voor verdere discussie oa hier).

Zonder die hele discussie aan te willen wakkeren, een count(*) oftewel tel alle rijen combineren met een methodiek van pak maar een willekeurige rij voor eigenschap_type lijkt me heel erg raar.

Had er count(e.id) gestaan dan had ik me er nog theoretisch iets bij voor kunnen stellen ( maar count(*) is op myisam weer sneller dan 1 kolom tellen Correctie van glowmouse leidde tot wat speurwerk en nu wat doorstreepwerk omdat het hier niet van toepassing is, wat ik zei gaat enkel op voor simpele tabel statistieken zonder where etc.)

Maar dit begint echt tegennatuurlijk te worden. Wat moet het resultaat van de count(*) worden als er geen functionele eis tussen e.id en e.eigenschap_type was?
Bij dit soort kleine query's valt er nog wel uit te komen, maar grotere query's met daarin subquery's waarin weer losse counts zitten etc. Daar komt dan op een gegeven moment echt geen mens meer uit. Ik gok zo maar even dat bij ingewikkelde query;s niemand meer durft te zeggen wat er nou geteld wordt...

[ Voor 8% gewijzigd door Gomez12 op 17-09-2009 00:27 ]

[GlowMouse]

Gomez12 schreef op donderdag 17 september 2009 @ 00:21:
[...]

Zonder die hele discussie aan te willen wakkeren, een count(*) oftewel tel alle rijen combineren

count(*) telt niet alle rijenkolommen, die telt al zodra er een rij is. De daadwerkelijke data van alle rijenkolommen hoeft niet opgehaald te worden. Zie ook mijn vorig antwoord.

[ Voor 6% gewijzigd door GlowMouse op 17-09-2009 00:30 . Reden: haal rijen/kolommen door elkaar ;( ]

[pedorus]

Gomez12 schreef op donderdag 17 september 2009 @ 00:21:
Wat moet het resultaat van de count(*) worden als er geen functionele eis tussen e.id en e.eigenschap_type was?

Wat moet het resultaat worden als de data willekeurig is opgeslagen in de verkeerde kolommen in een onzinnig design terwijl de hemel naar beneden valt Ik snap het probleem niet; zeker voor testen kan eigenschap_type worden weggelaten worden in de group by door gebruik te maken van de functionele relatie id->eigenschap_type. Dit voldoet zelfs gewoon aan de iso-standaard. En de als-vraag 'wat krijg ik voor resultaat als ik me niet aan het design hou' is een gekke natuurlijk.

Als je het over de count(*) vs count(e.id)-discussie met GlowMouse hebt, en per ongelijk mij hebt gequote ipv GlowMouse, dan zou ik zeggen zie oa hier. Aangezien ik er vanuit ga dat een id niet null kan zijn is het resultaat exact hetzelfde.

[Gomez12]

GlowMouse schreef op donderdag 17 september 2009 @ 00:25:
[...]

count(*) telt niet alle rijen, die telt al zodra er een rij is. De daadwerkelijke data van alle rijen hoeft niet opgehaald te worden. Zie ook mijn vorig antwoord.

Tja, maar als je definitie van een rij al gedeeltelijk ongedefinieerde data bevat, wat wordt er dan geteld? Het aantal rijen zonder dat je precies kan aangeven wat er in een rij zit? Lekker duidelijk...

Maar voor alle halve group by liefhebbers, ga er lekker mee door als het jullie niet uitmaakt. Je komt er vanzelf een keer mee in de knoop met of ongedefinieerde resultaten of gewoon een systeem wat niet om te zetten is naar een echte db. ( En nee, strict group by aanzetten is niet de oplossing )

pedorus schreef op donderdag 17 september 2009 @ 00:33:
[...]
Wat moet het resultaat worden als de data willeukeurig is opgeslagen in de verkeerde kolommen in een onzinnig design terwijl de hemel naar beneden valt Ik snap het probleem niet, zeker voor testen kan eigenschap_type worden weggelaten worden in de group by door gebruik te maken van de functionele relatie id->eigenschap_type. Dit voldoet zelfs gewoon aan de iso-standaard. En de als-vraag 'wat krijg ik voor resultaat als ik me niet aan het design hou' is een gekke natuurlijk.

Meestal wordt een design niet enkel afgesproken maar ook afgedwongen.
Een dba die in een 3-jaar oud design even enkele data-dingetjes wil corrigeren houdt zich misschien niet aan het design, maar als dit nergens zichtbaar is tot er een half jaar opeens onzin resultaten uit de dbase komen dan vindt ik het laatste toch best wel erg... Maar voor de rest van de discussie, zie hierboven voor mijn mening. Ik ben er ondertussen wel klaar mee.

[ Voor 39% gewijzigd door Gomez12 op 17-09-2009 00:38 ]

[pedorus]

Gomez12 schreef op donderdag 17 september 2009 @ 00:34:
Tja, maar als je definitie van een rij al gedeeltelijk ongedefinieerde data bevat, wat wordt er dan geteld? Het aantal rijen zonder dat je precies kan aangeven wat er in een rij zit? Lekker duidelijk...

Het toevoegen van willekeurige data aan het select-stuk verandert over het algemeen het resultaat van een count(*) in diezelfde select niet, dus deze snap ik niet.

Maar voor alle halve group by liefhebbers, ga er lekker mee door als het jullie niet uitmaakt. Je komt er vanzelf een keer mee in de knoop met of ongedefinieerde resultaten of gewoon een systeem wat niet om te zetten is naar een echte db. ( En nee, strict group by aanzetten is niet de oplossing )

Ik stel voorlopig alleen voor om hiermee te testen. No offence.

Meestal wordt een design niet enkel afgesproken maar ook afgedwongen.

Ik neem aan dat id een primary key is die de functionele relatie id->eigenschap_type afdwingt. Dit is een aanname. Het zou mooi zijn om de output van show create table van alledrie de tabellen te hebben.

offtopic:
Misschien ligt mijn reactiesnelheid te hoog voor dit tijdstip

[_js_]

pedorus schreef op donderdag 17 september 2009 @ 00:48:
[...]

Het toevoegen van willekeurige data aan het select-stuk verandert over het algemeen het resultaat van een count(*) in diezelfde select niet, dus deze snap ik niet.

[...]

Ik stel voorlopig alleen voor om hiermee te testen. No offence.

[...]

Ik neem aan dat id een primary key is die de functionele relatie id->eigenschap_type afdwingt. Dit is een aanname. Het zou mooi zijn om de output van show create table van alledrie de tabellen te hebben.

offtopic:
Misschien ligt mijn reactiesnelheid te hoog voor dit tijdstip

Uit de explain blijkt dat e.id in ieder geval de eerste waarde is van de index genaamd PRIMARY, dus die aanname zal wel correct zijn.

[cariolive23]

Waarom gaat dit met een DISTINCT wel snel? Ik dacht dat ik altijd moest oppassen met het gebruik van een distinct, en zo waar mogelijk distinct vervangen door group by?

Die twee doen verschillende dingen, mag je niet met elkaar vergelijken.

Ik heb de query ook even gedraait op de productie server (een server die staat bij byte.nl). Daar duurt die ook gewoon 8 seconden, het gaat hier om ongeveer evenveel rijen die in de tabellen staan

Dit zegt niks over de configuratie en de hoeveelheid RAM die de query mag gebruiken. Wanneer de query er voor zorgt dat de server resultaten naar disk moet swappen, wordt de boel altijd langzaam. Het snelheidsverschil tussen RAM en disk is enorm. Wanneer er moet worden gesorteerd op resultaten die van en naar disk moet komen/gaan, dan is de performance echt voltooid verleden tijd.

Moet GROUP BY gooi jij ook de COUNT uit jouw query en dus ook het sorteren op het resultaat van de COUNT. En dit resultaat kan onmogelijk in een index staan, dus moet er altijd sequentieel worden gesorteerd.

Ga je dus even verdiepen in de configuratie en hoe je dit runtime kan aanpassen. Kijk in elk geval even naar de sort_buffer_size

Each thread that needs to do a sort allocates a buffer of this size. Increase this value for faster ORDER BY or GROUP BY operations

En laat jij nu zowel een GROUP BY als een ORDER BY in je query hebben staan.

[Meijuh]

En laat jij nu zowel een GROUP BY als een ORDER BY in je query hebben staan.

Ik zal eens naar die configuratie instellingen kijken, maar het lijkt me in eerste instantie beter om eigenschap.eigenschap_type en respondent_has_eigenschap.eigenschap_waarde te veranderen van text naar varchar, aangezien ik gewoon geen text veld nodig heb voor deze velden.

[cariolive23]

maar het lijkt me in eerste instantie beter om eigenschap.eigenschap_type en respondent_has_eigenschap.eigenschap_waarde te veranderen van text naar varchar, aangezien ik gewoon geen text veld nodig heb voor deze velden.

Een goed datamodel is uiteraard onmisbaar! VARCHAR en TEXT zijn in MySQL twee verschillende datatypes die totaal verschillende invloeden hebben op het gebruik van indexen.

Let er bij het testen wel even op dat je niet zit te staren naar resultaten uit cache, dit klinkt mij als cache in de oren:

en dit gaat gewoon in 0.0027

[Meijuh]

Let er bij het testen wel even op dat je niet zit te staren naar resultaten uit cache, dit klinkt mij als cache in de oren:

Als ik "FLUSH TABLES" uitvoer, is dat goed genoeg?

[GlowMouse]

Meijuh schreef op donderdag 17 september 2009 @ 10:56:
[...]


Als ik "FLUSH TABLES" uitvoer, is dat goed genoeg?

SQL_NO_CACHE in je query opnemen. En wil je nog naar mijn eerste post kijken?

[Meijuh]

Zelf zou ik hem denk ik zo doen, met een index op (e.id,e.eigenschap_type), op (r.panel_id) en op (re.respondent_id).

Ik heb nu een index gemaakt (e.id, e.eigenschap_type) met de naam "ideigenschap" zoals je hieronder in de afbeeldingen kan zien.
Maar moet ik nu iets doen als:

SQL:

ALTER TABLE respondent ADD FOREIGN KEY (panel_id) REFERENCES eigenschap(ideigenschap)

en dan net zo voor re.respondent_id?


of:

SQL:

ALTER TABLE eigenschap ADD FOREIGN KEY (ideigenschap) REFERENCES respondent(panel_id)

en dan net zo voor re.respondent_id?

SQL:

SELECT
    e.id,
    e.eigenschap_type,
    COUNT(e.id) AS aantal
FROM
  eigenschap AS e
  JOIN
   (SELECT eigenschap_id
    FROM respondent r
    JOIN respondent_has_eigenschap AS re ON (re.respondent_id = r.id)
    WHERE r.panel_id = 35) AS tmp ON tmp.eigenschap_id = e.id 
GROUP BY
    e.id, 
    e.eigenschap_type
ORDER BY
    aantal DESC;

Ik voerde deze query uit (ik heb alleen die indices nog niet toegevoegd), de query duurde 9 seconden.

quote: Gomez12

2e poging:

SQL:

select eigenschap.id, eigenschap.eigenschap_type, tmp.aantal
from eigenschap
inner join (select e.id, count(*) as aantal
    from eigenschap as e 
         inner join respondent_has_eigenschap as re on e.id=re.eigenschap_id
         inner join respondent as r on (r.panel_id=35 and re.respondent_id=r.id)
    group by e.id) as tmp on (tmp.id= eigenschap.id)
order by aantal desc

duurde 0.9 seconden..

En ik mis nog steeds een duidelijk overzicht van je huidige indices. Zonder uitleg kan ik weinig met je explain output.

Komt het: (volgens mij geven de kardinaliteiten van de primary keys wel aan hoe groot de tabel is (heb ik gecontroleerd)...







edit: (mbt de cache)

SQL:

SELECT SQL_NO_CACHE DISTINCT e.id, e.eigenschap_type FROM eigenschap e 
JOIN respondent_has_eigenschap re ON re.eigenschap_id = e.id 
JOIN respondent r ON (re.respondent_id = r.id AND r.panel_id = 35)

duurt 0.0020 seconden.

SQL:

SELECT SQL_NO_CACHE e.id, e.eigenschap_type FROM eigenschap e 
JOIN respondent_has_eigenschap re ON re.eigenschap_id = e.id 
JOIN respondent r ON (re.respondent_id = r.id AND r.panel_id = 35)
GROUP BY e.id, e.eigenschap_type

duurt 3.0606 seconden

[ Voor 11% gewijzigd door Meijuh op 17-09-2009 11:50 . Reden: queries beter uitgeschreven ]

[cariolive23]

Waarom schrijf je nu iedere keer weer totaal andere queries? Je hebt nu een query met een GROUP BY die nergens op slaat. Dat deze vreemde query dan 3 seconden duurt, prachtig, maar is totaal niet relevant.

Edit: "andere queries" mag je lezen als "queries die andere resultaten ophalen, andere doelen hebben".

Bij de slager een kilo gehakt halen gaat ook veel sneller dan 25 ton bakstenen bij de Gamma afhalen...

[ Voor 34% gewijzigd door cariolive23 op 17-09-2009 11:54 ]

[GlowMouse]

Wat gek dat die ene query zoveel sneller is. Volgens de manual http://dev.mysql.com/doc/...istinct-optimization.html worden de queries op equivalente wijze uitgevoerd.

En heb je een dump van je data zodat we zelf kunnen spelen?

[Meijuh]

cariolive23 schreef op donderdag 17 september 2009 @ 11:52:
Waarom schrijf je nu iedere keer weer totaal andere queries? Je hebt nu een query met een GROUP BY die nergens op slaat. Dat deze vreemde query dan 3 seconden duurt, prachtig, maar is totaal niet relevant.


Edit: "andere queries" mag je lezen als "queries die andere resultaten ophalen, andere doelen hebben".

Ik wil gewoon aantonen dat het met GROUP BY zoveel langer duurt, ik heb toch gewoon die GROUP BY nodig?
Ik heb eigenlijk die count(*) AS aantal nodig, dus ik hoef inderdaad niet te kijken of die DISTINCT sneller is, maargoed dat was alleen om te testen.

Wat gek dat die ene query zoveel sneller is. Volgens de manual http://dev.mysql.com/doc/...istinct-optimization.html worden de queries op equivalente wijze uitgevoerd.

En heb je een dump van je data zodat we zelf kunnen spelen?

Hmm ik zou je graag een dump geven, maar in die 3 tabellen staan heel veel gegevens van respondenten en bevat dus privacy gevoelige data, ik weet niet of ik daar zo een dump van mag maken voor jullie, dat zou ik dan eerst even moeten overleggen op mijn werk.

Maar ik vind het echt supertof dat jullie hier zoveel tijd voor willen nemen

edit: waar kan ik makkelijk +/ 50 MB uploaden, als ik de dump mag maken?

[GlowMouse]

Meijuh schreef op donderdag 17 september 2009 @ 12:03:
[...]

edit: waar kan ik makkelijk +/ 50 MB uploaden, als ik de dump mag maken?

Ik zou sowieso de privacygevoelige info eruit halen. Alle namen wissen. De dump wordt dan kleiner, en gezipt blijft er weinig over. Die paar MB kun je overal uploaden.

[cariolive23]

En de database configuratie? Heb je daar nu al duidelijkheid over? We kunnen wel met z'n allen gaan testen, maar iedereen kan weer een andere configuratie hebben en dus andere resultaten krijgen.

[Meijuh]

cariolive23 schreef op donderdag 17 september 2009 @ 12:13:
En de database configuratie? Heb je daar nu al duidelijkheid over? We kunnen wel met z'n allen gaan testen, maar iedereen kan weer een andere configuratie hebben en dus andere resultaten krijgen.

Wat wil je hier precies van weten? Hoe de systeemvariabelen staan ingesteld of zo? Hoe kan ik jou die informatie geven?

[cariolive23]

Nou, ik heb al e.e.a. gezegt over de sort_buffer_size, wat is daar de instelling van? En heb je al eens geprobeerd om met andere instellingen van de sort_buffer_size jouw query uit te voeren? Uit jouw testjes blijkt dat GROUP BY al een probleem is, met betere instellingen van de sort_buffer_size zou je dit probleem misschien kunnen oplossen. Maar dat zul je dus wel even moeten proberen.

Ps. Een database is niet een ding die automagisch altijd precies doet wat jij wilt dat het moet doen en altijd onder iedere omstandigheid de maximale prestaties levert. Je moet daarin keuzes maken, een DBA is daarvoor de aangewezen persoon.

En ja, ik ben een DBA maar (gelukkig) niet voor MySQL-database.

[ Voor 28% gewijzigd door cariolive23 op 17-09-2009 12:41 ]

[Meijuh]

cariolive23 schreef op donderdag 17 september 2009 @ 12:40:
Nou, ik heb al e.e.a. gezegt over de sort_buffer_size, wat is daar de instelling van? En heb je al eens geprobeerd om met andere instellingen van de sort_buffer_size jouw query uit te voeren? Uit jouw testjes blijkt dat GROUP BY al een probleem is, met betere instellingen van de sort_buffer_size zou je dit probleem misschien kunnen oplossen. Maar dat zul je dus wel even moeten proberen.

Nou, ik heb de hele database met mysql administrator gekopieerd naar mijn localhost, zodat ik met een account met super priveleges de sort_buffer_size kon aanpassen.

Ik heb dus de volgende query uitgevoerd:

SQL:

SELECT SQL_NO_CACHE e.id, e.eigenschap_type, count(*) AS aantal FROM eigenschap e 
JOIN respondent_has_eigenschap re ON re.eigenschap_id = e.id 
JOIN respondent r ON (re.respondent_id = r.id AND r.panel_id = 35)
GROUP BY e.id, e.eigenschap_type
ORDER BY aantal DESC

Dit duurt ongeveer 5 seconden.

Hierna heb ik de velden eigenschap.eigenschap_type en respondent_has_eigenschap.eigenschap_waarde veranderd naar een varchar(255)

Nogmaals heb ik de query uitgevoerd en was de query ongeveer een halve seconde sneller.

Hierna heb ik sort_buffer_size op 4294967295 gezet, dus ongeveer 8000x zo hoog als deze stond (op mijn localhost stond deze nadat ik xampp had geinstalleerd op 500k).

Nogmaals heb ik de query uitgevoerd, deze duurde nog steeds 5 seconden met de nieuwe instellingen.

edit:

SQL:

SELECT SQL_NO_CACHE e.id, e.eigenschap_type
FROM eigenschap e
JOIN respondent_has_eigenschap re ON re.eigenschap_id = e.id
JOIN respondent r ON ( re.respondent_id = r.id
AND r.panel_id =35 )
GROUP BY e.id, e.eigenschap_type

duurt 2.7 seconden

[ Voor 8% gewijzigd door Meijuh op 17-09-2009 13:58 ]

[cariolive23]

En toch weer een GROUP BY die nergens voor nodig is... Wat is nu de bedoeling? Dat je een snelle query krijgt die je nooit nodig hebt? Of een query waar je wat mee kan? Testen op je eigen machine is leuk, maar alleen wanneer je de resultaten vergelijkt met andere resultaten op deze machine. Misschien is een query van 5 seconden op jouw eigen PC wel razendsnel en wordt deze op server in 0.005 seconden uitgevoerd.

Daarnaast zijn er nog vele andere parameters in de configuratie, deze moet je afstemmen op jouw databasegebruik.

Ga gestructureerd te werk, anders zul je nooit tot een oplossing komen.

zodat ik met een account met super priveleges de sort_buffer_size kon aanpassen.

Volgens mij kan dat altijd, kun je dit runtime aanpassen. Maar ik heb al enige tijd niets meer met MySQL gedaan, het kan zijn dat ik het mis heb.

[Meijuh]

cariolive23 schreef op donderdag 17 september 2009 @ 14:07:
En toch weer een GROUP BY die nergens voor nodig is... Wat is nu de bedoeling? Dat je een snelle query krijgt die je nooit nodig hebt?

ALs ik die count(*) as aantal nodig heb, moet ik toch een GROUP BY doen?

[cariolive23]

ALs ik die count(*) as aantal nodig heb, moet ik toch een GROUP BY doen?

Klopt, maar waarom laat je de COUNT dan weg wanneer je deze wel nodig hebt?

Zet de COUNT er dan in en ga dan pas testen. Testen met een query die maar de helft van je benodigde resultaten ophaalt en waarvan je weet dat COUNT daar significante invloed op heeft, dat heeft natuurlijk geen enkele zin.

[Meijuh]

quote: cariolive23

Zet de COUNT er dan in en ga dan pas testen. Testen met een query die maar de helft van je benodigde resultaten ophaalt en waarvan je weet dat COUNT daar significante invloed op heeft, dat heeft natuurlijk geen enkele zin.

Heb ik toch gedaan?:

Meijuh schreef op donderdag 17 september 2009 @ 13:57:

Ik heb dus de volgende query uitgevoerd:

SQL:

SELECT SQL_NO_CACHE e.id, e.eigenschap_type, count(*) AS aantal FROM eigenschap e 
JOIN respondent_has_eigenschap re ON re.eigenschap_id = e.id 
JOIN respondent r ON (re.respondent_id = r.id AND r.panel_id = 35)
GROUP BY e.id, e.eigenschap_type
ORDER BY aantal DESC

........

duurt 2.7 seconden

[cariolive23]

In jouw edit van 13:58 staat geen COUNT, die had ik gezien. Vandaar dat ik ook zeg je wat structuur moet aanbrengen in het testen en fixen, je bent nu lukraak dingen aan het proberen, hierdoor ontstaat spraakverwarring. Je gooit ook twee verschillende tijden doorelkaar, de ene keer duur de query ongeveer 5 seconden en de andere keer 2.7 seconden. Wat is het nu?

Ook ontbreekt een EXPLAIN van een goede query op de nieuwe omgeving met nieuwe settings, we hebben dus weer geen idee wat er gebeurt bij het uitvoeren van de query.

Maar goed, de originele query is niet zo spannend, die moet op een goed geconfigureerde server met de juiste indexen binnen enkele honderdsten van een seconde de gewenste resultaten opleveren. Dat is je doel en dat moet je ook wel kunnen bereiken. Vraag desnoods een MySQL-DBA, die kan je hierbij helpen.

[Meijuh]

Ik zie dus zo snel geen manier om het aantal snel te berekenen.
Wat ik denk ik wel kan doen, is dat wanneer een eigenschap_waarde aan een eigenschap_id wordt toegekend, dat ik deze dan in een nieuw te definieren tabel zet met
(panel_id, eigenschap_id, aantal) en dat ik dat aantal elke keer verhoog?

edit @ hierboven ik zal het eens met iemand er proberen achter te komen...

[ Voor 11% gewijzigd door Meijuh op 17-09-2009 14:49 ]

[pedorus]

Meijuh schreef op donderdag 17 september 2009 @ 11:41:

SQL:

SELECT SQL_NO_CACHE DISTINCT e.id, e.eigenschap_type FROM eigenschap e 
JOIN respondent_has_eigenschap re ON re.eigenschap_id = e.id 
JOIN respondent r ON (re.respondent_id = r.id AND r.panel_id = 35)

duurt 0.0020 seconden.

SQL:

SELECT SQL_NO_CACHE e.id, e.eigenschap_type FROM eigenschap e 
JOIN respondent_has_eigenschap re ON re.eigenschap_id = e.id 
JOIN respondent r ON (re.respondent_id = r.id AND r.panel_id = 35)
GROUP BY e.id, e.eigenschap_type

duurt 3.0606 seconden

Dit is gek. Het belangrijkste verschil zou sorteren op e.id, e.eigenschap_type zijn als ik het zo zie (MySQL doet standaard een order by bij een group by). Je vraagt je dan bijna af wat dit doet:

SQL:

SELECT SQL_NO_CACHE res.id, res.eigenschap_type, 
    (SELECT COUNT(*) FROM eigenschap e 
        JOIN respondent_has_eigenschap re ON re.eigenschap_id = e.id 
        JOIN respondent r ON (re.respondent_id = r.id AND r.panel_id = 35)
        WHERE e.id=e.res
        GROUP BY e.id, e.eigenschap_type) as aantal
FROM (SELECT DISTINCT e.id, e.eigenschap_type FROM eigenschap e 
JOIN respondent_has_eigenschap re ON re.eigenschap_id = e.id 
JOIN respondent r ON (re.respondent_id = r.id AND r.panel_id = 35)) AS res;

Verder vraag ik me af hoelang de volledige versie doet dan;

SQL:

SELECT r.panel_id, e.id, COUNT(*) FROM respondent_has_eigenschap re 
    JOIN eigenschap e ON re.eigenschap_id = e.id 
    JOIN respondent r ON re.respondent_id = r.id
GROUP BY r.panel_id, e.id

500.000 rijen sorteren is nu ook niet zo heel veel. Heck, de hele 25 mb aan data doorgaan is niet 3 seconden werk als het goed is (met maatwerksoftware).
En waarom nog steeds met die e.eigenschap_type in de group by bij testen .

Meijuh schreef op donderdag 17 september 2009 @ 14:46:
Wat ik denk ik wel kan doen, is dat wanneer een eigenschap_waarde aan een eigenschap_id wordt toegekend, dat ik deze dan in een nieuw te definieren tabel zet met
(panel_id, eigenschap_id, aantal) en dat ik dat aantal elke keer verhoog?

Ja, dat kan altijd natuurlijk. Maar dat komt meestal pas nadat je het goed genormaliseerd geprobeerd hebt. Als je de data ergens geanonimiseerd upload (bijvoorbeeld alleen id's), dan gok ik dat iemand dit toch een stuk sneller kan krijgen zonder die extra moeite.

[Meijuh]

Je vraagt je dan bijna af wat dit doet:

SQL:

SELECT SQL_NO_CACHE res.id, res.eigenschap_type, 
    (SELECT COUNT(*) FROM eigenschap e 
        JOIN respondent_has_eigenschap re ON re.eigenschap_id = e.id 
        JOIN respondent r ON (re.respondent_id = r.id AND r.panel_id = 35)
        WHERE e.id=e.res
        GROUP BY e.id, e.eigenschap_type) as aantal
FROM (SELECT DISTINCT e.id, e.eigenschap_type FROM eigenschap e 
JOIN respondent_has_eigenschap re ON re.eigenschap_id = e.id 
JOIN respondent r ON (re.respondent_id = r.id AND r.panel_id = 35)) AS e;

Deze query werkt niet, er is geen alias res, als ik res vervang door e en e.res (in de WHERE) dan zegt mysql subquery returned more than one row. Wat wil je precies met deze query?

Verder vraag ik me af hoelang de volledige versie doet dan;

SQL:

SELECT r.panel_id, e.id, COUNT(*) FROM respondent_has_eigenschap re 
    JOIN eigenschap e ON re.eigenschap_id = e.id 
    JOIN respondent r ON re.respondent_id = r.id
GROUP BY r.panel_id, e.id

De hell, je hebt hier eigenschap.eigenschap_type helemaal weggelaten, deze query geeft precies hetzelfde resultaat (maar dan zonder eigenschap_type), maar dan in 0.7 seconden. Misschien nog vrij veel maar al een hele verbetering.

Overigens geeft de explain query:

SQL:

EXPLAIN SELECT r.panel_id, e.id, COUNT(*) FROM respondent_has_eigenschap re 
    JOIN eigenschap e ON re.eigenschap_id = e.id 
    JOIN respondent r ON re.respondent_id = r.id
GROUP BY r.panel_id, e.id

Het volgende resultaat:



Het enige wat ik kan zeggen over eigenschap.eigenschap_type is dat deze een UNIQUE index heeft. Heeft dit er wat mee te maken?

SQL:

SELECT SQL_NO_CACHE e.id, count(*) AS aantal FROM eigenschap e 
JOIN respondent_has_eigenschap re ON re.eigenschap_id = e.id 
JOIN respondent r ON (re.respondent_id = r.id AND r.panel_id = 35)
GROUP BY e.id
ORDER BY aantal DESC

Geeft nu een resultaat in 0.7 seconden ( ik heb r.panel_id = 35) toegevoegd.

edit: misschien kan ik nu met e.id e.eigenschap_type opnieuw opzoeken in de tabel eigenschap, dit zou dan vrij vlot moeten gaan lijkt me.

[ Voor 3% gewijzigd door Meijuh op 17-09-2009 19:11 ]

[Gomez12]

Meijuh schreef op donderdag 17 september 2009 @ 19:09:
[...]

SQL:

SELECT SQL_NO_CACHE e.id, count(*) AS aantal FROM eigenschap e 
JOIN respondent_has_eigenschap re ON re.eigenschap_id = e.id 
JOIN respondent r ON (re.respondent_id = r.id AND r.panel_id = 35)
GROUP BY e.id
ORDER BY aantal DESC

Geeft nu een resultaat in 0.7 seconden ( ik heb r.panel_id = 35) toegevoegd.

edit: misschien kan ik nu met e.id e.eigenschap_type opnieuw opzoeken in de tabel eigenschap, dit zou dan vrij vlot moeten gaan lijkt me.

Zie ik het nou verkeerd of is dit gewoon de subquery zoals je hem al om 11:41 postte?
Volgens mij moet die query die daar 0,9 seconden duurt gewoon werken voor jou, ik zie er zo snel geen fout in... En hij zoekt gewoon je eigenschap_type erbij...

Wil je het nog sneller dan moet je echt gewoon eens naar dbase setup / instellingen kijken, want 0,7 seconden voor dit aantal records op enkel ints die keys zijn is echt gewoon walgelijk langzaam. Daar zit dan echt een gruwelijke config-fout in. Zelfs geheugen problemen /swappen moet je amper tegenaan lopen met enkel ints.

Enkel stel ik vraagtekens bij een unique index op een textveld ( het beinvloed je select performance niet nadelig, maar ik vraag me erg af waarvoor deze is? 1 teken anders in 65.535 ( uit mijn hoofd ) betekent al een niet uniek record... )

[ Voor 11% gewijzigd door Gomez12 op 17-09-2009 20:49 ]

[pedorus]

Meijuh schreef op donderdag 17 september 2009 @ 19:09:
Deze query werkt niet, er is geen alias res, als ik res vervang door e en e.res (in de WHERE) dan zegt mysql subquery returned more than one row. Wat wil je precies met deze query?

as e had as res moeten zijn; verbeterd.

Gomez12 schreef op donderdag 17 september 2009 @ 20:45:
Zie ik het nou verkeerd of is dit gewoon de subquery zoals je hem al om 11:41 postte?
Volgens mij moet die query die daar 0,9 seconden duurt gewoon werken voor jou, ik zie er zo snel geen fout in... En hij zoekt gewoon je eigenschap_type erbij...

Klopt ongeveer, ze lijken sterk op elkaar. 0.2 seconden om maximaal 88 dingen erbij te zoeken lijkt me wat lang enkel. 0.7 sec in totaal is trouwens nog steeds wat lang, maar het is in ieder geval een beter plafond dan 8 seconden.

Wat ik zou proberen is een nieuwe gecombineerde index (panel_id, id) op respondents. En dan kijken of je die gebruikt krijgt in combinatie met de primary index van respondent_has_eigenschap (respondent_id,eigenschap_id). Dan red MySQL het volgende als het goed is zonder dat de tabellen zelf benaderd hoeven te worden:

SQL:

select re.eigenschap_id, count(*) as aantal
    from respondent as r inner join respondent_has_eigenschap as re 
        on re.respondent_id=r.id
    where r.panel_id=35
    group by re.eigenschap_id

Vervolgens is het natuurlijk een kleine stap om die maximaal 88 eigenschapjes erbij te zoeken. Ik zie trouwens bij de index respondent_has_eigenschap_FKIndex2 (eigenschap_id) een cardinality van 266, terwijl er maar 88 eigenschappen zijn Is dit MyIsam en moet de tabel maar eens opgeschoond worden? Of wordt het tijd om OPTIMIZE TABLE maar eens te gebruiken? Of hanteerde MySQL/PhpMyAdmin een gekke definitie van cardinality?

Enkel stel ik vraagtekens bij een unique index op een textveld ( het beinvloed je select performance niet nadelig, maar ik vraag me erg af waarvoor deze is? 1 teken anders in 65.535 ( uit mijn hoofd ) betekent al een niet uniek record... )

Och het gaat om 32 KB aan indexes, waarbij ideigenschap met afstand het meeste ruimte inneemt. Omdat ideigenschap toch niet wordt gebruikt, zou ik die weggooien.

[ Voor 7% gewijzigd door pedorus op 18-09-2009 00:36 . Reden: antwoord toegevoegd ]

[Meijuh]

SQL:

select re.eigenschap_id, count(*) as aantal
    from respondent as r inner join respondent_has_eigenschap as re 
        on re.respondent_id=r.id
    where r.panel_id=35
    group by re.eigenschap_id

Ah dit werkt nu mooi snel (0.2 seconde), hoe kan ik nu het beste e.eigenschap_type eraan koppelen, want als ik dit met een join doe duurt het gewoon weer 8 seconden. Misschien met een IN ?

[Gomez12]

Meijuh schreef op vrijdag 18 september 2009 @ 11:23:
[...]


Ah dit werkt nu mooi snel (0.2 seconde), hoe kan ik nu het beste e.eigenschap_type eraan koppelen, want als ik dit met een join doe duurt het gewoon weer 8 seconden. Misschien met een IN ?

Sorry, maar ondertussen misschien handleiding / basis cursus sql ( vergeet niet de basiscursus mysql rare dingen, want die speelt hierbij wel enigszins ) erbij pakken...

Je begon met zeggen dat het vrijwel onmogelijk was om de bestaande query dan 8 seconden te krijgen, je hebt snellere query's gekregen en nu ga je weer zeggen dat het 8 sec duurt?

Imho heb je ondertussen genoeg voorbeelden gekregen om zelf iets te brouwen, het mantra van 8 sec wat niet te versnellen is herhalen is imho nutteloos en voor mij persoonlijk frustrerend.

[Meijuh]

SQL:

SELECT tmp.aantal, tmp.eigenschap_id, e.eigenschap_type
FROM eigenschap AS e, (
SELECT re.eigenschap_id, count( * ) AS aantal
FROM respondent_has_eigenschap re
JOIN respondent r ON ( r.id = re.respondent_id
AND r.panel_id =35 )
GROUP BY re.eigenschap_id
) AS tmp
WHERE e.id = tmp.eigenschap_id
ORDER BY aantal DESC

werkt mooi snel:) bedankt voor de hulp mensen:)

[cariolive23]

Mooi dat het is opgelost, wel triest om te zien dat MySQL een subquery nodig heeft om zoiets simpels binnen redelijke tijd uit te kunnen voeren. Helemaal wanneer je bedenkt dat subqueries nu niet het sterkste punt zijn van MySQL...

Het probleem wordt mogelijk veroorzaakt door de gebrekkige mogelijkheden voor het gebruik van indexen in MySQL.

Ben blij dat ik verlost ben van deze ellende

[Gomez12]

Ach, zoals een oude baas van mij het wel eens omschreef : Gratis software kost meer dev-geld...

[pedorus]

cariolive23 schreef op zaterdag 19 september 2009 @ 14:24:
Mooi dat het is opgelost, wel triest om te zien dat MySQL een subquery nodig heeft om zoiets simpels binnen redelijke tijd uit te kunnen voeren. Helemaal wanneer je bedenkt dat subqueries nu niet het sterkste punt zijn van MySQL...

Nu ga je er wel vanuit dat hier de optimale oplossing gecreëerd is, he. Maar eigenlijk weten we niks over die oplossing behalve dat ie blijkbaar snel genoeg werkt (op zich mooi dat het nu goed werkt). Ik heb het vermoeden dat als je iemand eens goed naar geanonimiseerde data zou laten kijken, er een nog iets andere oplossing uit zou komen.

[cariolive23]

Gomez12 schreef op zaterdag 19 september 2009 @ 14:51:
Ach, zoals een oude baas van mij het wel eens omschreef : Gratis software kost meer dev-geld...

Maar de ene gratis software is de andere gratis software niet!

Ontwikkelen voor/met MySQL is een kostbare grap, dat is bekend. Veel beperkingen, veel fout gedrag en maar beperkt schaalbaar. Het is aan de testers om de fouten uit je queries te halen, wat rijkelijk laat is in het ontwikkeltraject en dus zeer kostbaar is.

PostgreSQL is nog ietsjes meer gratis van MySQL (zie de BSD-licentie) maar is vergelijkbaar met SQL Server en kan zelfs de strijd aan met Oracle. Zolang je geen extreme performance nodig hebt, voldoet PostgreSQL uitstekend en fouten in queries vliegen je direct om de oren. Niet pas nadat je de database fatsoenlijk hebt geconfigureerd.

Functioneel werkt PostgreSQL altijd hetzelfde en altijd gewoon goed. Je komt functioneel ook niets tekort, er zijn maar weinig dingen die PostgreSQL (evt. met plugin) niet kan.

[PolarBear]

cariolive23 schreef op woensdag 16 september 2009 @ 22:18:
Ik zou de JOIN's als JOIN uitschrijven, de WHERE ook naar een JOIN verplaatsen en de GROUP BY compleet maken. Veel verschil mag het niet maken, het is een query van niets. Ik zie niet waarom dit zolang zou moeten duren. Of gebruik je hardware van 15 jaar oud? Of heb je nog 100 andere queries op je databaseserver draaien?

Even een oude koe uit dit topic, maar serieus is dat nodig om de JOIN te verplaatsen? De query engine in MS SQL Server bouwt dat soort dingen zelf om, bij uitvoer geeft het nauwelijks tot geen verschil. Tevens geeft MS SQL Server 2008 aan welke indexen een bepaalde query zouden kunnen versnellen, een handige optie (mits verstandig toegepast), kent MySQL dat soort zaken niet?

[cariolive23]

kent MySQL dat soort zaken niet?

Nee, zelfs het gebruik van een subquery is al een uitdaging voor MySQL. Vandaar dat het mij verbaast dat de query mét subquery al veel sneller is dan de originele query, het moet dus nog wel sneller kunnen.

MySQL is functioneel zeer beperkt en totaal niet vergelijkbaar met bv. SQL Server of PostgreSQL. Deze databases zijn 100 keer slimmer en kunnen zelf bepalen hoe de query het meest efficient kan worden uitgevoerd.

De query engine in MS SQL Server bouwt dat soort dingen zelf om, bij uitvoer geeft het nauwelijks tot geen verschil.

Idem dito met PostgreSQL, het maakt nauwelijks verschil omdat PostgreSQL ook ziet dat dit hetzelfde is. Linksom of rechtsom, het is dezelfde vraag en wordt dus op dezelfde manier uitgevoerd. Wanneer je met EXPLAIN het queryplan zou opvragen, zie je dat ook. Maar ja, MySQL... Het is gratis maar kost je alleen drie dagen om een simpele query binnen redelijke tijd uit te laten voeren. Uiteindelijk betaal je dus de hoofdprijs voor deze database.

[Voutloos]

1. Dergelijke join condities in de where clause zetten levert je geen performance penalty op. Het is imo alleen veel duidelijk als je bij de join zelf zegt hoe je wil joinen.

2. Derived tables in mysql werken prima en kunnen vaak wel de snelste oplossing zijn.

3. Overige subqueries (niet deze dus) worden te vaak als dependant subquery elke keer opnieuw uitgevoerd en dat is inderdaad dus meestal retetraag. Prima punt op dat vlak, en helaas de reden dat je het soms beter dmv een derived table kan schrijven.