De eerste biertjes voor de donderdagmiddagborrel zijn net geopend, als de storingstelefoon om aandacht vraagt. Een van de databaseclusters heeft het erg zwaar. De veroorzaker is snel gevonden, maar de oorzaak is wat ingewikkelder. Het blijkt dat er vaak achter elkaar een hele eenvoudige query uitgevoerd wordt, op een behoorlijk grote tabel:


De tabel heeft dus ruim 12 miljoen records, wat veel is, maar ook weer niet heel veel. Zeker niet omdat de query die erop uitgevoerd wordt erg eenvoudig is, maar toch veel tijd in beslag neemt:

Bijna 9 seconden voor zo’n simpele query? Dat moet wel een indexeringsprobleem zijn. Aangezien er geselecteerd wordt op ArticleID, zou daar een index op gezet moeten zijn, maar gezien de tijd die de query kost zal die index er dus wel niet zijn.

He! Er is wel een index op dat veld. Dan zou MySQL hem ook moeten gebruiken, toch?

Aiai, dat ziet er niet goed uit. Ten eerste: onder ‘Extra’ staat: ‘Using temporary’. Dat betekent dat MySQL (een deel) van het resultaat van de query in een tijdelijke tabel opslaat. Als je geluk hebt past die tijdelijke tabel in het interne geheugen van de server. Past dat niet, en zeker bij zo’n grote tabel als deze zal dat niet lukken, wordt een tijdelijke tabel op disk opgeslagen worden, en dat is traaaaaag. Dat wil je dus te allen tijde voorkomen!
Ten tweede (en belangrijkste): MySQL ziet wel dat er een index is die hij zou kunnen gebruiken (ArticleID onder ‘possible_keys’), maar kiest ervoor om het niet te doen (NULL onder ‘key’). Dat is zeker niet goed, want MySQL maakt de afweging dat het niet gebruiken van die key hem winst zal opleveren. Dat betekent bijna altijd dat er iets verkeerd geconfigureerd is.
Even verder zoeken dus …

Aha! Het veld waarop gezocht wordt, ArticleID, is geconfigureerd als een VARCHAR, waarbij maximaal 100 karakters in het veld mogen staan. Dat wordt dus een enorme index, wat voor MySQL reden is om hem niet te gebruiken.
Als wij iets verder zoeken, blijkt dat er in het veld ArticleID alleen maar getallen opgeslagen worden, en geen letters. Het hoeft dus helemaal geen VARCHAR te zijn, maar kan gewoon een INT-veld zijn:

Laten we eens kijken wat dezelfde query nu voor resultaat oplevert:

Bingo! MySQL besluit nu wel om de index te gebruiken. Het is ook direct duidelijk dat MySQL maar 1 regel (van de 12+ miljoen!) hoeft te bekijken om te beslissen of de query een resultaat oplevert. Ook de ‘Using temporary’ is verdwenen. Dat zou dus betekenen dat de query zelf ook sneller is, toch?

Jazeker!

Conclusie: niet alleen indexering is belangrijk (wat hier goed toegepast was), maar het is ook erg belangrijk om de juiste datatypes voor je velden te kiezen. Alleen dan kan MySQL een goede strategie bepalen om de query uit te voeren. Voor iedereen die zelf queries schrijft is goede kennis van indexering en vooral van EXPLAIN een absolute must!

Leuk leesvoer:

Scan je eigen Magento shop op veiligheidslekken