[SQL] Wat is sneller?

[pierre-oord]

Ik 2 tabellen met een userid-relatie.

Nu wil ik, als in 1 tabel minstens 1 rij met een bepaalde waarde voorkomt, in de andere tabel een waarde op TRUE zetten, en als er geen een voorkomt, op false.

Het kan echter al zijn, dat de TRUE/FALSE waarde die ik ergens wil gaan neer zetten, al op TRUE of FALSE staat.

Dit idee dus:
UPDATE `tabel` SET waarde=1 WHERE userid=1

En dan doen:
UPDATE `tabel` SET waarde=1 WHERE userid=1

Nu mijn vraag:
Is het sneller om
UPDATE `tabel` SET waarde=1 WHERE userid=1 AND waarde=0

te doen? Want dan "update" ik dus niet de velden die al op 1 staan. Of doet MySQL dit gewoon automatisch?

[Michali]

Ik denk dat MySQL zulke optimalisaties zelf wel uitvoert. Als je er over twijfelt kun je het altijd benchen. Voer de queries eens, zeg, 1000 keer uit en kijk wat sneller is.

[ Voor 3% gewijzigd door Michali op 01-08-2006 12:08 ]

[whoami]

Je laatste query zal nooit werken want je hebt 2 where's.

Wat is er sneller ? Dat hangt er vanaf. Ik zou zeggen: gebruik de WHERE clausule die het snelste is, en in dit geval zal dit de WHERE clausule zijn waarin enkel op userid getest wordt.

Tenzij je echt een reden hebt om de records waarvoor waarde al op 1 staat nog niet up te daten, zou ik de eerste query nemen. Als je echter bv een update trigger op die tabel hebt staan, die iets uitvoert met de gewijzigde records, dan is het imo beter om je 2de oplossing te nemen.

Echter, als je puur wil weten wat het snelste is: test het uit. Volgens mij zal het de eerste query zijn, als je geen index hebt liggen op de column 'waarde' (waar ik dus vanuit ga).

Ik denk dat MySQL zulke optimalisaties zelf wel uitvoert.

Dat zou me wel enigszins verwonderen, aangezien het toch om een andere WHERE clausule gaat, en hoe je het nu draait of keert, het DBMS moet rekening houden met die 2 condities. Zowiezo zal hij die checks moeten doen.

[ Voor 16% gewijzigd door whoami op 01-08-2006 12:10 ]

[lier]

Onder voorbehoud:

Je doet in ieder geval al een controle op het ID veld (via de index).
Een extra where clause lijkt eerder vertragend, maar het voorkomt wel de updates op die rijen. Verwacht daarom (als je je voorbeeld even corrigeert, want daar klopt niets van) dat het sneller wordt. Het heeft namelijk een beperking op je mutaties en dus je log/file system tot gevolg.

[ Voor 13% gewijzigd door lier op 01-08-2006 12:10 ]

[P_de_B]

Een index op waarde zal niet uitmaken, hij is niet selectief genoeg (1 of 0). Ik betwijfel trouwens of een index op een boolean veld wel mogelijk is. (In MSSQL iig niet op een bitveld)

Ik gok dat het de eerste is die sneller zal zijn, maar zoals men hierboven zegt: test het en bekijk de EXPLAIN.

[OverSoft]

Als ik via SQLyog zoiets uitvoer geeft hij altijd een bepaald aantal affected rows terug (dit haalt ie uit MySQL). Die affected rows zijn altijd de rows die de waarde op iets anders hadden staan, ipv die al op 1 staan.

Oftewel, hij voert zelf die controle al uit.

[Michali]

whoami schreef op dinsdag 01 augustus 2006 @ 12:09:
Dat zou me wel enigszins verwonderen, aangezien het toch om een andere WHERE clausule gaat, en hoe je het nu draait of keert, het DBMS moet rekening houden met die 2 condities. Zowiezo zal hij die checks moeten doen.

Maar als een veld de waarde 1 heeft, en je gaat hem updaten naar waarde 1, dan lijkt me dat MySQL eerst de waarde controleert voordat hij hem overschrijft. De controle is volgens mij een stuk sneller dan het schrijven. De where clause wordt natuurlijk wel altijd gecontroleerd, dus mogelijk is die query nog langzamer dan (wat jij dus ook zegt).

[pierre-oord]

OverSoft schreef op dinsdag 01 augustus 2006 @ 12:10:
Als ik via SQLyog zoiets uitvoer geeft hij altijd een bepaald aantal affected rows terug (dit haalt ie uit MySQL). Die affected rows zijn altijd de rows die de waarde op iets anders hadden staan, ipv die al op 1 staan.

Oftewel, hij voert zelf die controle al uit.

Kijk! En explain, had ik ook kunnen gebruiken denk ik, helemaal niet aan gedacht!

Bedankt in ieder geval! Ik ga misschien nog even benchmarken, en dan de query proberen, hij is behoorlijk groot (voor zover ik grotere queries maak )

Bedankt!

[whoami]

Maar als een veld de waarde 1 heeft, en je gaat hem updaten naar waarde 1, dan lijkt me dat MySQL eerst de waarde controleert voordat hij hem overschrijft.

Dat zou me wel ten zeerste verbazen... Nu , ik heb hier geen MySQL, en ik test het even met MS - SQL Server.

Daar heb ik eens een simpele update query gemaakt, die er zo uit ziet:

code:

update testtabel set waarde = 'bliep' where id = 1

Ook als de waarde van de column 'waarde' al 'bliep' is, krijg ik als output

1 row(s) affected

Ik heb de test nog wat verder gedreven en een UPDATE trigger op de tabel geplaatst. Deze trigger gaat de oude versie van het record in een history tabel gaan plaatsen, en ook hier kan je zien dat het record zeker geupdated werd: er wordt nl. een record geinsert in de history tabel.

[kenneth]

OverSoft schreef op dinsdag 01 augustus 2006 @ 12:10:
Als ik via SQLyog zoiets uitvoer geeft hij altijd een bepaald aantal affected rows terug (dit haalt ie uit MySQL). Die affected rows zijn altijd de rows die de waarde op iets anders hadden staan, ipv die al op 1 staan.

Oftewel, hij voert zelf die controle al uit.

Vaag gedrag, zeg. Als ik een UPDATE wil doen, en MySQL vind van niet, dan doet hij hem dus niet?

[Michali]

whoami schreef op dinsdag 01 augustus 2006 @ 12:30:
Dat zou me wel ten zeerste verbazen... Nu , ik heb hier geen MySQL, en ik test het even met MS - SQL Server.

Dan werkt MySQL toch iets anders. Ik heb even een test tabel gemaakt met 1 veld (VARCHAR(255)) en daarin 2 records gestopt met de waarden 1 en 0. Als ik dan een update doe waarbij ik de waarde op 1 zet voor beide records (gewoon update tabel set waarde = 1), dan krijg ik terug dat er 1 affected row is. Dit is ook wat OverSoft zei. Ik ben nu ook wel benieuwd ga het nog iets uitgebreider testen.

[ Voor 29% gewijzigd door Michali op 01-08-2006 12:54 ]

[Janoz]

Weet je zeker dat je test juist gegaan is? Dit lijkt me namelijk behoorlijk ongewesnt gedrag. Aan de andere kant verbaast het mij ook niks dat mysql weer eens een loopje neemt met met van alles en nog wat.

Als ik zeg dat de database alle records die aan conditie x voldoen aan moet passen dan moet hij alle records met conditie x aanpassen. Intern mag dat best geoptimaliseerd worden, maar dat moet niet betekenen dat ik dat terug zie in mijn metadata.

Na wat neuzen op php blijkt het inderdaad het geval te zijn...

[Cavorka]

Inderdaad, gisteren heb ik toevallig ook een uur zitten zoeken waarom ik maar 0 affected rows terug kreeg van MySQL op het moment dat ik een rij wilde updaten... Dus als je een rij update maar dezelfde waardes erin zet, dan zegt hij 0 affected rows.

Grrrr!! Talk about unexpected behaviour.

[kenneth]

MySQL ... redefining DBMSes since 1995

[Michali]

Ik heb even een test uitgevoerd.

Mijn bevindingen zijn dat query:

SQL:

UPDATE tabel SET value = 1 WHERE value = 0

zo'n 20% sneller is dan query:

SQL:

UPDATE tabel SET value = 1

Maar dit is wel alleen als de query vaak achter elkaar wordt uitgevoerd. Als hij enkel 1 keer wordt uitgevoerd, dan lijkt de tweede juist vaak een paar procent sneller. (Maar dat wisselt ook af en toe.) Pas bij ongeveer 10 keer of meer achter elkaar uitvoeren kom je aan je 20% voordeel. Bij minder loopt het dus af en bij meer gaat het niet verder dan die 20%.

Eigenlijk geheel tegen mijn verwachting in. Zo zie je maar weer dat je nooit op je gevoel moet afgaan als het op performance aankomt. Gewoon benchen en kijken wat sneller is.