Optimalisatie van indexen en queries

Vorig weekend gaf ik op Barcamp een uiteenzetting over optimalisaties van sites, waaronder het goed tunen van je database, en het toevoegen van indexes waar nodig. Vandaag kreeg ik een vraag van een collega-sysadmin om een index-probleem op te lossen.

De database bevat de data voor een MTA-setup, en regelmatig moet voor een domain, vanuit de MTA-software, een domain herleid worden naar het “hoofddomain” (denk “hoe moet de mail voor open-minds.be’ behandeld worden”, het antwoord is “zoals openminds.be”.

Bekijken we even de (vereenvoudigde) tabelstructuur:

CREATE TABLE `transport` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`domain` varchar(128) NOT NULL DEFAULT ’’,
PRIMARY KEY (`id`),
UNIQUE KEY `domain` (`domain`)
)

en de aliasen:

CREATE TABLE `domain_aliases` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`transport_id` int(11) NOT NULL,
`alias` varchar(128) NOT NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `alias` (`alias`),
KEY `transport_id` (`transport_id`)
)

Data voor de tabellen ziet er als volgt uit:

---------------------
| id | domain       |
---------------------
|  1 | openminds.be | 
|  2 | wonko.be     | 
---------------------
-------------------------------------
| id | transport_id | alias         |
-------------------------------------
|  1 |            1 | open-minds.be | 
|  2 |            2 | blonko.be     | 
-------------------------------------

Postfix probeert enkele waarden te matchen door telkens een deel van de “destination” te reduceren… zo worden ‘bernard@openminds.be’ en ‘@openminds.be’ geprobeerd.

De query die voorgesteld werd was de volgende:
SELECT concat('@',transport.domain) FROM transport INNER JOIN domain_aliases ON domain_aliases.transport_id = transport.id WHERE CONCAT('@',domain_aliases.alias) = '@open-minds.be';

Hier hebben we echter een probleem, want we zien dat er op de tabel domain_aliases geen index gebruikt kan worden, aangezien er een functie gebruikt wordt op de waarde van de rij, die telkens geëvalueerd dient te worden. Deze database opdracht is dus niet efficiënt.

Wanneer we echter de where aanpassen naar WHERE domain_aliases.alias = SUBSTRING_INDEX('@open-minds.be','@',-1);, zien we dat er wel indexen gebruik kunnen worden. De reden is heel eenvoudig. De uitkomst van de functie is niet langer afhankelijk van de rij waarop de where toegepast wordt, maar enkel op gekende data, en deze kan dus voor het uitvoeren van de query geëvalueerd worden…

Op onze voorbeelddata is dit natuurlijk niet echt spectaculair, maar deze setup dient verschillende duizenden domeinen en aliasen te bevatten, en dan zal het verschil zeker merkbaar zijn…

Tip: Gebruik de EXPLAIN-functionaliteit van mysql om het verschil te zien.

Er zijn nog andere optimalisaties mogelijk, die betrekking hebben op de normalisatie van data, maar daarover gaan we het even niet hebben…

Optimalisatie is dus duidelijk een samenspel tussen de DBA en de applicatiebeheerder!