MySQLi - INDEXES

Un index de base de données est une structure de données qui améliore la vitesse des opérations dans une table. Les index peuvent être créés en utilisant une ou plusieurs colonnes, fournissant la base à la fois à des recherches aléatoires rapides et à un ordre efficace d'accès aux enregistrements.

Lors de la création de l'index, il convient de considérer quelles sont les colonnes qui seront utilisées pour effectuer des requêtes SQL et créer un ou plusieurs index sur ces colonnes.

En pratique, les index sont également des types de tables, qui conservent la clé primaire ou le champ d'index et un pointeur vers chaque enregistrement dans la table réelle.

Les utilisateurs ne peuvent pas voir les index, ils sont juste utilisés pour accélérer les requêtes et seront utilisés par Database Search Engine pour localiser les enregistrements très rapidement.

Les instructions INSERT et UPDATE prennent plus de temps sur les tables ayant des index alors que les instructions SELECT deviennent rapides sur ces tables. La raison en est que lors de l'insertion ou de la mise à jour, la base de données doit également insérer ou mettre à jour les valeurs d'index.

Index simple et unique

Vous pouvez créer un index unique sur une table. Un index unique signifie que deux lignes ne peuvent pas avoir la même valeur d'index. Voici la syntaxe pour créer un index sur une table.

CREATE UNIQUE INDEX index_name ON table_name ( column1, column2,...);

Vous pouvez utiliser une ou plusieurs colonnes pour créer un index. Par exemple, nous pouvons créer un index sur tutorials_inf en utilisant NAME_INDEX.

CREATE UNIQUE INDEX NAME_INDEX ON tutorials_inf(name);

Vous pouvez créer un index simple sur une table. Omettez simplement le mot clé UNIQUE de la requête pour créer un index simple. L'index simple permet de dupliquer les valeurs dans une table.

Si vous souhaitez indexer les valeurs d'une colonne dans l'ordre décroissant, vous pouvez ajouter le mot réservé DESC après le nom de la colonne.

mysql> CREATE UNIQUE INDEX NAME_INDEX ON tutorials_inf (name DESC);

Commande ALTER pour ajouter et supprimer INDEX

Il existe quatre types d'instructions pour ajouter des index à une table -

  • ALTER TABLE tbl_name ADD PRIMARY KEY (column_list) - Cette instruction ajoute une PRIMARY KEY, ce qui signifie que les valeurs indexées doivent être uniques et ne peuvent pas être NULL.

  • ALTER TABLE tbl_name ADD UNIQUE index_name (column_list) - Cette instruction crée un index pour lequel les valeurs doivent être uniques (à l'exception des valeurs NULL, qui peuvent apparaître plusieurs fois).

  • ALTER TABLE tbl_name ADD INDEX index_name (column_list) - Cela ajoute un index ordinaire dans lequel toute valeur peut apparaître plusieurs fois.

  • ALTER TABLE tbl_name ADD FULLTEXT index_name (column_list) - Cela crée un index FULLTEXT spécial qui est utilisé à des fins de recherche de texte.

Voici l'exemple pour ajouter un index dans une table existante.

mysql> ALTER TABLE tutorials_inf ADD INDEX (id);

Vous pouvez supprimer n'importe quel INDEX en utilisant la clause DROP avec la commande ALTER. Essayez l'exemple suivant pour supprimer l'index créé ci-dessus.

mysql> ALTER TABLE tutorials_inf DROP INDEX (c);

Vous pouvez supprimer n'importe quel INDEX en utilisant la clause DROP avec la commande ALTER. Essayez l'exemple suivant pour supprimer l'index créé ci-dessus.

Commande ALTER pour ajouter et supprimer la CLÉ PRIMAIRE

Vous pouvez également ajouter une clé primaire de la même manière. Mais assurez-vous que la clé primaire fonctionne sur les colonnes, qui ne sont PAS NULL.

Voici l'exemple pour ajouter une clé primaire dans une table existante. Cela créera d'abord une colonne NOT NULL, puis l'ajoutera en tant que clé primaire.

mysql>  ALTER TABLE tutorials_inf MODIFY id INT NOT NULL;
mysql> ALTER TABLE tutorials_inf ADD PRIMARY KEY (id);

Vous pouvez utiliser la commande ALTER pour supprimer une clé primaire comme suit:

mysql> ALTER TABLE tutorials_inf DROP PRIMARY KEY;

Pour supprimer un index qui n'est pas une CLÉ PRIMAIRE, vous devez spécifier le nom de l'index.

Affichage des informations INDEX

Vous pouvez utiliser la commande SHOW INDEX pour lister tous les index associés à une table. La sortie au format vertical (spécifié par \ G) est souvent utile avec cette instruction, pour éviter un long retour en ligne -

Essayez l'exemple suivant

mysql> SHOW INDEX FROM table_name\G
........