MySQL vragen over het gebruik van indices

[SvMp]

Met behulp van een index heb ik een zeer trage query zeer snel weten te krijgen. Ik heb me vervolgens verder verdiept in indices, maar ik heb nog wel wat vragen. Het gaat om MySQL 5.1.

Als voorbeeld gebruik ik de tabel 'content_file':

code:

Gemaakt met:

CREATE TABLE content_file (
   id INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
   content_id INTEGER UNSIGNED NOT NULL REFERENCES content(id),
   filename VARCHAR(127) NOT NULL,
   res_x SMALLINT UNSIGNED NOT NULL,
   res_y SMALLINT UNSIGNED NOT NULL,
   secure SMALLINT UNSIGNED NOT NULL,
   PRIMARY KEY (id),
   UNIQUE KEY (id)
);

Later nog indices toegevoegd.

mysql> explain content_file;
+------------+----------------------+------+-----+---------+----------------+
| Field      | Type                 | Null | Key | Default | Extra          |
+------------+----------------------+------+-----+---------+----------------+
| id         | int(10) unsigned     | NO   | PRI | NULL    | auto_increment |
| content_id | int(10) unsigned     | NO   | MUL | NULL    |                |
| filename   | varchar(127)         | NO   |     | NULL    |                |
| res_x      | smallint(5) unsigned | NO   |     | NULL    |                |
| res_y      | smallint(5) unsigned | NO   |     | NULL    |                |
| secure     | smallint(5) unsigned | NO   |     | NULL    |                |
+------------+----------------------+------+-----+---------+----------------+
6 rows in set (0.00 sec)

mysql> show index from content_file;
+--------------+------------+------------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| Table        | Non_unique | Key_name               | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment |
+--------------+------------+------------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| content_file |          0 | PRIMARY                |            1 | id          | A         |       15224 |     NULL | NULL   |      | BTREE      |         |
| content_file |          0 | id                     |            1 | id          | A         |       15224 |     NULL | NULL   |      | BTREE      |         |
| content_file |          1 | content_id             |            1 | content_id  | A         |        7612 |     NULL | NULL   |      | BTREE      |         |
| content_file |          1 | content_id_res_x_res_y |            1 | content_id  | A         |        7612 |     NULL | NULL   |      | BTREE      |         |
| content_file |          1 | content_id_res_x_res_y |            2 | res_x       | A         |       15224 |     NULL | NULL   |      | BTREE      |         |
| content_file |          1 | content_id_res_x_res_y |            3 | res_y       | A         |       15224 |     NULL | NULL   |      | BTREE      |         |
+--------------+------------+------------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------

1. Is hier sprake van duplicate of redundant indices?
Volgens mij alleen PRIMARY en id omdat ik bij het maken van de tabel id zowel als primary als unique key heb gedefiniëerd. Ik dacht dat dit zo moest, maar volgens http://www.mysqlperforman...es-and-redundant-indexes/ moet dat niet.
Moet ik UNIQUE achterwege laten bij primary-keys?

2. Vervolg op de eerste vraag: Stel ik heb unieke velden, maar het is geen noodzaak om er een index van te maken. Kan ik dat aangeven? Unique wordt namelijk altijd ook een index.

Stel ik heb een tabel met de velden ID en naam. ID is primary key, op naam zelf wordt nooit gezocht maar het is wel een veld dat altijd uniek moet zijn. Als ik "UNIQUE KEY naam" toevoeg aan de definitie, dan wordt naam ook een index, terwijl dat niet nodig is. Wat is een goede werkwijze? Geen uniek constraint definieren?

3. Naamgeving: Index hebben een naam (keyname). Als ik zelf een index maak, maakt het dan wat uit welke naam ik toeken? Of is dat alleen voor index-hinting?

4. Op de bovenstaande voorbeeld-tabel "content_file" worden gegevens opgehaald op basis van content_id en de combinatie content_id, res_x en res_y.
Queries in de vorm van:
SELECT * FROM content_file WHERE content_id=?
of
SELECT * FROM content_file WHERE content_id=? AND res_x<? AND res_x>? AND res_y<? AND res_y>?

Welke indices zijn hier nodig? De indices die ik in het voorbeeld noem?

Kan voor die tweede query ook volstaan worden met drie afzonderlijke indices content_id, res_x en res_y of moeten ze samen in één index?
Stel je hebt een index met de kolommen "content_id, res_x, res_y" zoals ook in dit voorbeeld, is dan nog een afzonderlijke index "content_id" nodig voor de eerste query?

Gaat die tweede query automatisch de index "content_id, res_x, res_y" gebruiken? Ik heb de keyname "content_id_res_x_res_y" gebruikt. Heeft dat nog invloed? Is hier hinting nodig?

[NMe]

SvMp schreef op donderdag 17 november 2011 @ 15:16:
1. Is hier sprake van duplicate of redundant indices?

Nee, maar je hebt wel een index gemaakt op élk veld van je tabel. Hoe nuttig is een index nog als je ze op elk veld gooit?

Moet ik UNIQUE achterwege laten bij primary-keys?

Een primary key is per definitie van zichzelf al uniek.

2. Vervolg op de eerste vraag: Stel ik heb unieke velden, maar het is geen noodzaak om er een index van te maken. Kan ik dat aangeven? Unique wordt namelijk altijd ook een index.

Je wil geen full table scan moeten doen om te checken of een waarde al in de tabel staat, daarom is het een index. Gewoon unique indices gebruiken dus.

3. Naamgeving: Index hebben een naam (keyname). Als ik zelf een index maak, maakt het dan wat uit welke naam ik toeken? Of is dat alleen voor index-hinting?

Is voornamelijk handig voor jezelf, dus dat mag je tot in zekere mate zelf weten. Bedrijven hebben mogelijk naming conventions waar je je aan moet houden.

4. Kan voor die tweede query ook volstaan worden met drie afzonderlijke indices content_id, res_x en res_y of moeten ze samen in één index?

Losse indices zouden hier voldoen. Bij gecombineerde indices faalt MySQL sowieso redelijk, het kijkt per tabel alleen naar de eerste kolom van de index.

Stel je hebt een index met de kolommen "content_id, res_x, res_y" zoals ook in dit voorbeeld, is dan nog een afzonderlijke index "content_id" nodig voor de eerste query?

Voor MySQL sowieso wel, voor andere database engines zul je gecombineerde indices kunnen maken waar wél goed naar gekeken wordt.

Gaat die tweede query automatisch de index "content_id, res_x, res_y" gebruiken? Ik heb de keyname "content_id_res_x_res_y" gebruikt. Heeft dat nog invloed? Is hier hinting nodig?

De naam doet dus niks, en MySQL gaat deze key niet goed kunnen gebruiken. Met losse indices zou je query iets sneller moeten zijn.

[SvMp]

@NMe: Bedankt voor je uitgebreide reactie.

Nee, maar je hebt wel een index gemaakt op élk veld van je tabel. Hoe nuttig is een index nog als je ze op elk veld gooit?

Niet op filename en security. Dit lijkt mij niet zo extreem, want de waardes filename en security worden gevonden op basis van drie criteria dus zit je al snel met relatief veel indices.

Je wil geen full table scan moeten doen om te checken of een waarde al in de tabel staat, daarom is het een index. Gewoon unique indices gebruiken dus.

Dit argument heb ik zelf ook bedacht, maar ook heel snel ontkracht. Zo zijn er tabellen waarvan bij voorbaat bekend is dat deze niet veel rijen zullen gaan bevatten. Daarnaast kan er sprake zijn van data die vooral gelezen wordt en alleen geschreven via bijv. een back-office. Dat het schrijven iets langer duurt, is meestal geen probleem. Vandaar dat ik die vraag wel stelde.

Losse indices zouden hier voldoen. Bij gecombineerde indices faalt MySQL sowieso redelijk, het kijkt per tabel alleen naar de eerste kolom van de index.

Dus drie afzonderlijke indices op content_id, res_x en res_y volstaan als ik in mijn WHERE-clause zowel content_id, res_x als res_y gebruik? Worden deze automatisch gebruikt door MySQL?

Voor MySQL sowieso wel, voor andere database engines zul je gecombineerde indices kunnen maken waar wél goed naar gekeken wordt.

Dus het gebruik van gecombineerde indices is zinloos bij MySQL?
Als ik een PRIMARY KEY heb bestaande uit meerdere velden, wordt er ook een gecombineerde index gemaakt. Is deze van ook zinloos?

[NMe]

SvMp schreef op donderdag 17 november 2011 @ 16:26:
Niet op filename en security.

Klopt, ik keek verkeerd. My bad.

Dit argument heb ik zelf ook bedacht, maar ook heel snel ontkracht. Zo zijn er tabellen waarvan bij voorbaat bekend is dat deze niet veel rijen zullen gaan bevatten. Daarnaast kan er sprake zijn van data die vooral gelezen wordt en alleen geschreven via bijv. een back-office. Dat het schrijven iets langer duurt, is meestal geen probleem. Vandaar dat ik die vraag wel stelde.

Zelfs al was er geen enkel ander voordeel, dan nog is het afdwingen van uniqueness nog genoeg voordeel op zichzelf om het wel te doen.

Dus drie afzonderlijke indices op content_id, res_x en res_y volstaan als ik in mijn WHERE-clause zowel content_id, res_x als res_y gebruik? Worden deze automatisch gebruikt door MySQL?

Als het goed is wel, maar dat kan een EXPLAIN op je query je vertellen.

Dus het gebruik van gecombineerde indices is zinloos bij MySQL?
Als ik een PRIMARY KEY heb bestaande uit meerdere velden, wordt er ook een gecombineerde index gemaakt. Is deze van ook zinloos?

Combined keys zijn zeker niet zinloos en juist bij een compound primary key zoals je hier omschrijft is het handig. Ik ben er laatst echter wel achter gekomen dat je dan een extra index moet zetten op enkel het tweede veld van je primary key omdat je anders alsnog een performance hit pakt. Door deze specifieke verbetering heb ik een query die letterlijk 2 minuten nodig had verkort naar een fractie van een seconde.

[GlowMouse]

Je kunt SHOW CREATE TABLE gebruiken om een mooie tabeldefinitie te krijgen.

Bij gecombineerde indices faalt MySQL sowieso redelijk, het kijkt per tabel alleen naar de eerste kolom van de index.

Onzin.

Voor MySQL sowieso wel

Onzin.

Nee, maar je hebt wel een index gemaakt op élk veld van je tabel. Hoe nuttig is een index nog als je ze op elk veld gooit?

Onzin. Zowel de index met de naam id is redundant (want identiek aan de PK), als de index met de naam content_id (want het is een prefix van de index (content_id,res_x,res_y).

SELECT * FROM content_file WHERE content_id=?
of
SELECT * FROM content_file WHERE content_id=? AND res_x<? AND res_x>? AND res_y<? AND res_y>?

Voor de eerste query heb je een index nodig die begint met content_id (zoals content_id,res_x,res_y). Voor de tweede query ook, en als tweede veld danwel res_x danwel res_y (je index wordt dus (content_id,res_x) of (content_id,res_y). Je hebt er weinig aan om beide in dezelfde index te stoppen voor deze query. Doe bijvoorbeeld eens: SELECT * FROM content_file ORDER BY content_id,res_x,res_y. Je kunt daar heel snel de rijen in terugvinden die voldoen aan content_id=? AND res_x<? AND res_x>?. Om vervolgens de rijen te vinden die tevens voldoen aan res_y<? AND res_y>? heb je er niks aan dat de resultaten ook gesorteerd zijn op res_y. Je had dus net zo goed SELECT * FROM content_file ORDER BY content_id,res_x kunnen doen. Hieruit leid je af dat de index op (content_id,res_x) voldoende is. Je ziet ook dat je hieruit snel de rijen haalt die voldoen aan "WHERE content_id=?". Een aparte index op content_id is dus inderdaad niet nodig. Je kunt nu zelf bedenken wanneer een index op (content_id,res_y) beter is dan een index op (content_id,res_x)

[NMe]

GlowMouse schreef op donderdag 17 november 2011 @ 19:23:
Onzin.

Vooruit, iets genuanceerder dan:

If a multiple-column index exists on col1 and col2, the appropriate rows can be fetched directly. If separate single-column indexes exist on col1 and col2, the optimizer attempts to use the Index Merge optimization (see Section 7.3.1.4, “Index Merge Optimization”), or attempts to find the most restrictive index by deciding which index excludes more rows and using that index to fetch the rows.

If the table has a multiple-column index, any leftmost prefix of the index can be used by the optimizer to find rows. For example, if you have a three-column index on (col1, col2, col3), you have indexed search capabilities on (col1), (col1, col2), and (col1, col2, col3).

Met een index op col1, col2 kun je dus wél op enkel col1 filteren in je where en op een combinatie van col1 en col2, maar zoek je alleen op col2, dan kan hij de index ineens niet meer gebruiken. Vooral bij primary indices wil dat nog wel eens een "oh shit"-momentje opleveren als de database groter wordt.

Ook was er iets met het gebruiken van gecombineerde indices en joins, al kan ik zo snel even niet terugvinden wat daar het probleem mee was.

[ Voor 5% gewijzigd door NMe op 17-11-2011 19:55 ]

[GlowMouse]

NMe schreef op donderdag 17 november 2011 @ 19:54:
Met een index op col1, col2 kun je dus wél op enkel col1 filteren in je where en op een combinatie van col1 en col2, maar zoek je alleen op col2, dan kan hij de index ineens niet meer gebruiken. Vooral bij primary indices wil dat nog wel eens een "oh shit"-momentje opleveren als de database groter wordt.

Zo werken B-tree's, het is geen falen van MySQL. Met mijn uitleg dmv ORDER BY zie je ook snel waarom je niet op res_x kunt zoeken ondanks de index op (content_id,res_x).
De “Index Merge Optimization” is iets waar je niet om moet rekenen, het is een slecht lapmiddel. De tip om losse indices te gebruiken zou ik daarom niet onderschrijven. Een goede multicolumn index werkt veel sneller.

Ook was er iets met het gebruiken van gecombineerde indices en joins, al kan ik zo snel even niet terugvinden wat daar het probleem mee was.

Er schiet mij niks tebinnen. Ze kunnen in ieder geval prima gebruikt worden, zowel in de tabel waarop je joint als in de tabel die gejoind wordt.

[cariolive23]

SvMp schreef op donderdag 17 november 2011 @ 15:16:
SELECT * FROM content_file WHERE content_id=?
of
SELECT * FROM content_file WHERE content_id=? AND res_x<? AND res_x>? AND res_y<? AND res_y>?

Welke indices zijn hier nodig? De indices die ik in het voorbeeld noem?

content_id is een goede kandidaat voor een index, je doet een vergelijking waarbij de waarde en de parameter aan elkaar gelijk moeten zijn.

Voor de andere waardes ligt dit totaal anders, met een > (groter dan) en een < (kleiner dan) hangt het helemaal af van de parameter of het gebruik van een index wel zin heeft. Stel dat 80% van de waardes in de tabel groter zijn dan de parameter, wil je hier zeer waarschijnlijk géén indexscan laten uitvoeren, dat zou het waarschijnlijk alleen maar langzamer maken. Dit hangt overigens weer af van hoe e.e.a. is opgeslagen, wat er in RAM staat en hoeveel IO er beschikbaar is. Teveel "het hangt er vanaf" om hier een eenduidig antwoord op te geven.

Gaat die tweede query automatisch de index "content_id, res_x, res_y" gebruiken? Ik heb de keyname "content_id_res_x_res_y" gebruikt. Heeft dat nog invloed?

Dat hangt van veel zaken af, voornamelijk de parameters en de distributie.

Is hier hinting nodig?

Hinting moet je proberen te vermijden, alleen gebruiken wanneer de database de plank volkomen misslaat en op geen enkele manier de juiste kant op te sturen is. Een hint kan vandaag heel goed werken en morgen voor slechte performance zorgen. En overmorgen wél weer goed werken... Voorzichtig mee omgaan dus, het is geen silver bullet.

En EXPLAIN is je beste vriend.

Ps. Mocht je kunnen updaten naar versie 5.5, doe dit dan. Die is weer slimmer geworden.

[SvMp]

Dank voor alle reacties, daarmee is het een leerzaam topic geworden.

Ik zit toch nog wel met die indexes die worden gemaakt voor unique keys. Unique keys gaan altijd verplicht gepaard met een index, ik wil graag unique constraints zonder index.

Voorbeeld:

SQL:

CREATE TABLE plaatssoort (
   code INTEGER UNSIGNED NOT NULL,
   land_iso3166numeric INTEGER UNSIGNED NOT NULL REFERENCES land(iso3166numeric),

   vanaf INTEGER UNSIGNED NOT NULL,
   tot INTEGER UNSIGNED NOT NULL,

   tekst INTEGER UNSIGNED NOT NULL REFERENCES tekst(nr),
   weergave VARCHAR(250) NOT NULL,

   PRIMARY KEY (code, land_iso3166numeric, vanaf),
   UNIQUE (code, land_iso3166numeric, tot)
);

Land en code zijn voor een bepaalde periode van toepassing. Deze periode wordt vastgelegd met 'vanaf' en 'tot'. Er kunnen meerdere periodes zijn met dezelfde code, eventueel met een andere waarde voor tekst of weergave.

Er mag geen overlap zijn tussen periodes voor een code in een bepaald land.

Uniek is dus de combinatie code, land en vanaf. Maar óók code, land en tot. Alleen deze twee unieke combinaties tezamen garanderen de afwezigheid van overlapping.

In bovenstaande definitie heb ik een primary key met 'vanaf' en unique key met 'tot'. Die unique key levert een totaal overbodige index op. Tot nu toe heb ik het standpunt dat ik de unique beter achterwege kan laten. Deze tabel wordt gegenereerd vanuit een XML, de software die de tabel vult kan overlappingen die per vergissing in de XML staan wel ontdekken. Dat die key van belang is om de uniciteit snel te kunnen controleren is hier niet van belang, aangezien de tabel alleen af en toe beschreven wordt vanuit een xml bestand en dat mag wel een seconde langer duren als het moet.

NB: Overigens garanderen de twee unique keys niet de afwezigheid van overlap.
Voorbeeld: 1965 tot 1980 en van 1970 tot 1985 kan nog steeds, overlap van 1970 tot 1980 terwijl alle jaartallen uniek zijn. De unique key is dus sowieso zinloos.

[ Voor 9% gewijzigd door SvMp op 19-11-2011 11:06 ]

[cariolive23]

Een overlap is in MySQL niet te voorkomen zonder table locks, MySQL kent hier geen constraints voor. Je moet dan dus de tabel locken, checken of er sprake is van overlap en zo niet, dan het nieuwe record toevoegen.

Je zou uiteraard ook data dubbel kunnen gaan opslaan, om zo te voorkomen dat er gaten gaan ontstaan. Dan kun je wel met een unique constraint werken, je hoeft niet meer te controleren op een overlap omdat je namelijk alle jaren gaat invullen. Je krijgt extra data in de database, maar dat is niet anders. Let er wel op dat je dit binnen één transaction doet, anders kan het nog steeds fout gaan.

In PostgreSQL heb je deze functionaliteit wel, een exclusion constraint. (beschikbaar sinds versie 9.0) Op Stackoverflow heeft iemand een aardig voorbeeld gegeven. Een exclusion constraint gebruikt overigens ook wel een index, GiST ligt het meeste voor de hand, om voor snelheid te zorgen.

Mocht je PostgreSQL 9.x kunnen gebruiken, dan is dit de oplossing: Geen dubbele data, betrouwbaar en snel.

[NMe]

Dus je wil de UNIQUE die je wél nodig hebt opofferen alleen omdat die een index toevoegt die je niet nodig hebt? Vind ik niet logisch.

[SvMp]

NMe schreef op zaterdag 19 november 2011 @ 13:51:
Dus je wil de UNIQUE die je wél nodig hebt opofferen alleen omdat die een index toevoegt die je niet nodig hebt? Vind ik niet logisch.

In dit geval maakt het niet uit, omdat die unique constraint de lading niet volledig dekt.
Combinaties zoals 1960 - 1975 en 1970 - 1980 kunnen dan nog steeds. Volgens mij bestaat er geen constraint waarmee dit te voorkomen is.

[NMe]

Nee, maar dat heeft dan ook niks met uniek zijn te maken maar met overlap.

[cariolive23]

SvMp schreef op zondag 20 november 2011 @ 14:03:
[...]
Volgens mij bestaat er geen constraint waarmee dit te voorkomen is.

Klopt, MySQL kent geen check constraints en kan geen overlap voorkomen. Dat moet je met MySQL dus in de applicatiecode zien te voorkomen door extra queries uit te voeren en table locks te gebruiken, of migreren naar een database die de gewenste functionaliteit wel in huis heeft.

UNIQUE <> OVERLAP