CREATE TABLE Animal (
id SMALLINT UNSIGNED NOT NULL AUTO_INCREMENT,
espece VARCHAR(40) NOT NULL,
sexe CHAR(1),
date_naissance DATETIME NOT NULL,
nom VARCHAR(30),
commentaires TEXT,
PRIMARY KEY (id),
INDEX ind_date_naissance (date_naissance),
INDEX ind_nom (nom(10)),
UNIQUE INDEX ind_uni_nom_espece (nom, espece) -- Index sur le
nom et l'espece
)
ENGINE=INNODB;
Avec cette syntaxe, KEY ne peut être utilisé que pour les index simples et INDEX pour tous les types d'index. Bon, je
sais que c'est assez pénible à retenir, mais en gros, utilisez INDEX partout, sauf pour définir un index dans la
description même de la colonne, et vous n'aurez pas de problème.
Ajout des index après création de la table
En dehors du fait que parfois vous ne penserez pas à tout au moment de la création de votre table, il peut parfois être intéressant
de créer les index après la table.
En effet, je vous ai dit que l'ajout d'index sur une table ralentissait l'exécution des requêtes d'écriture (insertion, suppression,
modification de données). Par conséquent, si vous créez une table, que vous comptez remplir avec un grand nombre de données
immédiatement, grâce à la commande LOAD DATA INFILE par exemple, il vaut bien mieux créer la table, la remplir, et ensuite
seulement créer les index voulus sur cette table.
Il existe deux commandes permettant de créer des index sur une table existante : ALTER TABLE, que vous connaissez déjà un
peu, et CREATE INDEX. Ces deux commandes sont équivalentes, utilisez celle qui vous parle le plus.
Ajout avec ALTER TABLE
Code : SQL
ALTER TABLE nom_table
ADD INDEX [nom_index] (colonne_index [, colonne2_index ...]); -Ajout d'un index simple
ALTER TABLE nom_table
ADD UNIQUE [nom_index] (colonne_index [, colonne2_index ...]); -Ajout d'un index UNIQUE
ALTER TABLE nom_table
ADD FULLTEXT [nom_index] (colonne_index [, colonne2_index ...]); -Ajout d'un index FULLTEXT
Contrairement à ce qui se passait pour l'ajout d'une colonne, il est ici obligatoire de préciser INDEX (ou UNIQUE, ou
FULLTEXT) après ADD.
Dans le cas d'un index multi-colonnes, il suffit comme d'habitude de toutes les indiquer entre parenthèses, séparées par des
virgules.
Reprenons la table Test_tuto, utilisée pour tester ALTER TABLE, et ajoutons-lui un index sur la colonne nom :
Code : SQL
ALTER TABLE Test_tuto
ADD INDEX ind_nom (nom);
Si vous affichez maintenant la description de votre table Test_tuto, vous verrez que dans la colonne "Key", il est indiqué MUL
pour la colonne nom. L'index a donc bien été créé.
Partie 2 : Index, jointures et sous-requêtes
78/414
www.openclassrooms.com
id SMALLINT UNSIGNED NOT NULL AUTO_INCREMENT,
espece VARCHAR(40) NOT NULL,
sexe CHAR(1),
date_naissance DATETIME NOT NULL,
nom VARCHAR(30),
commentaires TEXT,
PRIMARY KEY (id),
INDEX ind_date_naissance (date_naissance),
INDEX ind_nom (nom(10)),
UNIQUE INDEX ind_uni_nom_espece (nom, espece) -- Index sur le
nom et l'espece
)
ENGINE=INNODB;
Avec cette syntaxe, KEY ne peut être utilisé que pour les index simples et INDEX pour tous les types d'index. Bon, je
sais que c'est assez pénible à retenir, mais en gros, utilisez INDEX partout, sauf pour définir un index dans la
description même de la colonne, et vous n'aurez pas de problème.
Ajout des index après création de la table
En dehors du fait que parfois vous ne penserez pas à tout au moment de la création de votre table, il peut parfois être intéressant
de créer les index après la table.
En effet, je vous ai dit que l'ajout d'index sur une table ralentissait l'exécution des requêtes d'écriture (insertion, suppression,
modification de données). Par conséquent, si vous créez une table, que vous comptez remplir avec un grand nombre de données
immédiatement, grâce à la commande LOAD DATA INFILE par exemple, il vaut bien mieux créer la table, la remplir, et ensuite
seulement créer les index voulus sur cette table.
Il existe deux commandes permettant de créer des index sur une table existante : ALTER TABLE, que vous connaissez déjà un
peu, et CREATE INDEX. Ces deux commandes sont équivalentes, utilisez celle qui vous parle le plus.
Ajout avec ALTER TABLE
Code : SQL
ALTER TABLE nom_table
ADD INDEX [nom_index] (colonne_index [, colonne2_index ...]); -Ajout d'un index simple
ALTER TABLE nom_table
ADD UNIQUE [nom_index] (colonne_index [, colonne2_index ...]); -Ajout d'un index UNIQUE
ALTER TABLE nom_table
ADD FULLTEXT [nom_index] (colonne_index [, colonne2_index ...]); -Ajout d'un index FULLTEXT
Contrairement à ce qui se passait pour l'ajout d'une colonne, il est ici obligatoire de préciser INDEX (ou UNIQUE, ou
FULLTEXT) après ADD.
Dans le cas d'un index multi-colonnes, il suffit comme d'habitude de toutes les indiquer entre parenthèses, séparées par des
virgules.
Reprenons la table Test_tuto, utilisée pour tester ALTER TABLE, et ajoutons-lui un index sur la colonne nom :
Code : SQL
ALTER TABLE Test_tuto
ADD INDEX ind_nom (nom);
Si vous affichez maintenant la description de votre table Test_tuto, vous verrez que dans la colonne "Key", il est indiqué MUL
pour la colonne nom. L'index a donc bien été créé.
Partie 2 : Index, jointures et sous-requêtes
78/414
www.openclassrooms.com