Indexer les filtres et les jointures

Une requête qui recherche souvent des emprunts par date peut devoir examiner de nombreuses lignes si la table a grandi. Ajouter un index peut réduire ce travail, mais chaque index doit répondre à un accès réel et rester cohérent avec les contraintes déjà définies. Dans cette leçon, vous ajoutez un index simple à la médiathèque et inspectez les index des tables concernées.

Un index simple pour un filtre fréquent

Un index B-tree organise les valeurs d’une ou plusieurs colonnes afin que le moteur puisse retrouver plus directement celles qui correspondent à une recherche ou à une plage. Il ne remplace pas les lignes de la table : il constitue une structure supplémentaire que MariaDB peut choisir d’utiliser. La leçon précédente a établi une référence de performance ; conservez ses conditions de mesure pour comparer les effets d’un index.

Prenez une recherche des emprunts d’une date donnée. La colonne date_emprunt est déjà utilisée dans les exemples du projet ; l’index ci-dessous est donc placé sur cette seule colonne et nommé de façon à rendre son rôle identifiable. Dans le fichier sql/index.sql, ajoutez cette définition après la création des tables et le chargement du schéma.

-- Index simple pour les recherches par date d’emprunt
CREATE INDEX IF NOT EXISTS idx_emprunts_date_emprunt
    ON emprunts (date_emprunt);

-- Recherche de référence : emprunts effectués à cette date
SELECT emprunt_id, adherent_id, date_emprunt
FROM emprunts
WHERE date_emprunt = '2025-03-01';

La requête renvoie les emprunts enregistrés le 1er mars 2025. Si aucune ligne ne correspond, elle renvoie un résultat vide.

L’index est pertinent si cette recherche revient souvent et si la table contient suffisamment de lignes pour que la structure apporte un bénéfice. Il peut aussi aider pour certaines recherches par intervalle, par exemple entre deux dates, mais son efficacité dépend des données et du plan choisi par le moteur. Un index ne garantit donc pas, à lui seul, une accélération mesurable.

Index sur les colonnes de jointure

Une jointure rapproche des lignes à partir de colonnes liées. Dans le modèle de la médiathèque, emprunts.adherent_id relie les emprunts aux adhérents. Une clé étrangère protège cette relation ; InnoDB exige également un index utilisable sur la colonne référençante et peut le créer automatiquement si nécessaire. Vérifiez ce qui existe avant d’ajouter un index : un index déjà adapté rendrait un doublon inutile.

-- Inventaire des index présents sur les deux tables liées
SHOW INDEX FROM emprunts;
SHOW INDEX FROM adherents;

-- Jointure représentative sur la clé étrangère
SELECT emprunts.emprunt_id, adherents.nom, emprunts.date_emprunt
FROM emprunts
INNER JOIN adherents
    ON adherents.adherent_id = emprunts.adherent_id
WHERE emprunts.date_emprunt = '2025-03-01';

SHOW INDEX affiche notamment le nom de chaque index, les colonnes qui le composent et leur ordre. La jointure renvoie les emprunts de la date demandée avec le nom de leur adhérent.

Sélectivité et coût d’un index

La sélectivité décrit à quel point une colonne distingue les lignes : une valeur qui ne concerne qu’une petite part des emprunts est généralement plus discriminante qu’une valeur très fréquente. Un index sur une colonne presque identique pour toutes les lignes peut aider moins, selon la requête et les données. Évaluez cette caractéristique sur un jeu représentatif, sans conclure à partir de quelques lignes de test seulement.

Tout index consomme de l’espace et doit être entretenu lorsque des lignes sont insérées, modifiées ou supprimées. Gardez donc un index si une requête importante en tire un bénéfice mesuré ou si une contrainte le requiert, et documentez son objectif dans sql/index.sql. Pour l’instant, vous avez ajouté un index simple ; les index combinant plusieurs colonnes et leur ordre seront étudiés dans la prochaine leçon.

À retenir

• Choisissez un index simple à partir d’un filtre ou d’une jointure réellement utilisé dans le projet.
• Un index B-tree fournit une structure de recherche supplémentaire ; il ne garantit pas que MariaDB l’utilisera.
• Consultez SHOW INDEX avant d’ajouter un index sur une clé étrangère, car InnoDB peut déjà l’avoir créé.
• Tenez compte de la sélectivité, des mesures de référence, de l’espace consommé et du coût des écritures.
• Documentez chaque index dans sql/index.sql et vérifiez son usage avec EXPLAIN dans la prochaine étape.