Déclaration MySQL CREATE INDEX

L'instruction `CREATE INDEX` dans MySQL est utilisée pour créer un index sur une ou plusieurs colonnes d'un tableau afin d'améliorer la vitesse de récupération des requêtes. Les index sont essentiels pour optimiser les performances des bases de données, en particulier lorsqu'il s'agit de grands ensembles de données.

Utilisation

L'instruction `CREATE INDEX` est utilisée lorsque vous avez besoin d'améliorer les performances des opérations de recherche de données. Il est particulièrement utile pour accélérer les requêtes `SELECT` qui filtrent ou trient les données.

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

Dans cette syntaxe, `CREATE INDEX` définit le nom de l'index et spécifie le tableau et les colonnes sur lesquels l'index est créé.

Exemples

1. Création d'un index de base

CREATE INDEX idx_lastname
ON employees (last_name);

Cet exemple crée un index nommé `idx_lastname` sur la colonne `last_name` du tableau `employees`, optimisant ainsi les requêtes qui filtrent par les noms de famille.

2. Indice composite

CREATE INDEX idx_name_dob
ON employees (last_name, birth_date);

Cette syntaxe démontre la création d'un index composite sur `last_name` et `birth_date`, utile pour les requêtes filtrant sur les deux colonnes.

3. Unique Index

CREATE UNIQUE INDEX idx_email
ON users (email);

Cet exemple crée un index unique sur la colonne `email` du tableau `users` pour s'assurer que toutes les entrées d'email sont uniques et pour accélérer les recherches par email.

4. Index du texte intégral

CREATE FULLTEXT INDEX idx_content
ON articles (content);

Cet exemple crée un index `FULLTEXT` sur la colonne `content` du tableau `articles`, utile pour les recherches en texte intégral.

5. Index spatial

CREATE SPATIAL INDEX idx_location
ON locations (geo_point);

Ceci crée un index `SPATIAL` sur la colonne `geo_point` du tableau `locations`, utile pour les types de données géographiques.

Conseils et bonnes pratiques

• Indexer de manière sélective. Créez des index uniquement sur les colonnes qui font l'objet de recherches ou de tris fréquents afin d'éviter toute surcharge inutile.
• Contrôler les performances. Utilisez l'instruction `EXPLAIN` pour évaluer les performances des requêtes et déterminer si un index est utile.
• Considérez le type d'indice. Choisissez le type d'index approprié (par exemple, unique, plein texte, spatial) en fonction de vos données et de vos besoins d'interrogation.
• Équilibre entre lecture et écriture. Sachez que si les index accélèrent les lectures, ils peuvent ralentir les opérations d'écriture (INSERT, UPDATE, DELETE).
• Fonctionnement interne. La plupart des index utilisent des structures B-tree pour maintenir les données triées, ce qui facilite les recherches rapides.
• Limites. Comprenez les limitations telles que le nombre maximal d'index par tableau ou la taille maximale des clés d'index.
• Conventions d'appellation. Utilisez des noms clairs et descriptifs pour les index afin de maintenir la lisibilité, en les préfixant par exemple par `idx_`.
• Gérabilité. Examinez et gérez régulièrement les index en utilisant `ALTER TABLE` pour les modifier et `DROP INDEX` pour supprimer les index inutiles.

Gestion et suppression des index

Pour modifier un index, vous pouvez utiliser l'instruction `ALTER TABLE`. Pour supprimer un index, on utilise l'instruction `DROP INDEX`, comme indiqué ci-dessous :

DROP INDEX idx_lastname ON employees;

Considérations

• Clé primaire vs. Index unique. Une clé primaire est une contrainte qui garantit l'unicité des valeurs et la non-nullité, tandis qu'un index unique ne garantit que l'unicité.
• Colonnes à faible cardinalité. Évitez d'indexer des colonnes de faible cardinalité, car elles n'offrent pas nécessairement des avantages significatifs en termes de performances.
• Petits tableaux. Les index sur de très petits tableaux peuvent ne pas produire d'améliorations notables des performances en raison des frais généraux liés à la maintenance de l'index.