Construire des index composites dans le bon ordre

Une requête peut filtrer à la fois sur l’adhérent et sur une période, mais un index sur une seule colonne ne décrit pas nécessairement cet accès combiné. Un index composite permet d’organiser plusieurs colonnes dans une même structure. Vous allez choisir leur ordre à partir des requêtes réellement conservées dans le projet, sans encore interpréter leur plan d’exécution.

Dans la leçon précédente, vous avez ajouté idx_emprunts_date_emprunt sur emprunts(date_emprunt). Vous conservez cet index : il peut répondre aux recherches portant uniquement sur la date. Pour choisir un index supplémentaire, partez d’une requête concrète et notez les colonnes utilisées dans ses conditions WHERE, ainsi que les colonnes servant aux jointures.

Repérer les conditions d’accès

Considérez la recherche des emprunts d’un adhérent à partir d’une date donnée. Elle associe une égalité sur adherent_id à une plage sur date_emprunt. Ces deux colonnes correspondent aux conditions d’accès observées : l’identifiant isole un adhérent, puis la date limite les emprunts retenus.

-- Emprunts récents d’un adhérent donné.
SELECT emprunt_id, adherent_id, date_emprunt
FROM emprunts
WHERE adherent_id = 42
  AND date_emprunt >= '2025-01-01'
ORDER BY date_emprunt;

Pour cette forme de requête, l’ordre proposé est (adherent_id, date_emprunt) : l’égalité sur l’adhérent vient avant la plage de dates. Cet ordre est une décision adaptée à cet accès, pas une règle universelle. Si vos mesures de référence montrent que les recherches portent principalement sur des périodes, l’index simple déjà présent reste pertinent pour ces recherches, tandis que d’autres requêtes peuvent justifier un autre index.

Comprendre le préfixe gauche

Un index composite est ordonné d’abord par sa première colonne, puis par la suivante à l’intérieur des valeurs de la première. Avec (adherent_id, date_emprunt), le préfixe gauche comprend adherent_id seul, puis la combinaison des deux colonnes. La recherche par adhérent peut donc correspondre à cet index ; une recherche portant seulement sur la date ne bénéficie pas du même préfixe. C’est pourquoi l’index sur date_emprunt ne doit pas être supprimé sans avoir vérifié les autres requêtes qui en dépendent.

Ajouter l’index au projet

Ajoutez la définition suivante à sql/index.sql, à la suite de l’index simple existant. L’index est nommé explicitement et suit les conventions du projet. Si le script est relancé, MariaDB signalera que le nom existe déjà : n’exécutez donc cette création qu’une fois, ou contrôlez l’état du schéma avant de la rejouer.

-- Accélère les recherches d’emprunts par adhérent et date.
CREATE INDEX idx_emprunts_adherent_date
    ON emprunts (adherent_id, date_emprunt);

Après création, vérifiez la définition avec SHOW INDEX FROM emprunts;, comme dans la leçon sur les index simples. Vous devez retrouver les deux colonnes du nouvel index dans l’ordre annoncé, ainsi que l’index simple existant. Cette vérification confirme la structure créée ; elle ne prouve pas encore que MariaDB choisit cet index pour une requête donnée.

Évitez de multiplier les index proches sans raison mesurée. Chaque index supplémentaire doit être actualisé lors des écritures, et des index qui commencent par la même colonne peuvent se recouvrir pour certains accès. Conservez les requêtes Q1 et Q2, leurs conditions de mesure et les index présents : vous comparerez ensuite les choix sur les données représentatives, puis apprendrez à vérifier l’utilisation d’un index avec EXPLAIN.

À retenir

• Choisissez les colonnes d’un index composite à partir des filtres et jointures d’une requête observée.
• Placez généralement les conditions d’égalité avant la colonne utilisée pour une plage, lorsque cela correspond aux accès visés.
• L’ordre des colonnes détermine les préfixes utilisables : un index (A, B) ne remplace pas automatiquement un index commençant par B.
• Conservez les index existants tant que les requêtes qui les utilisent n’ont pas été vérifiées.
• Vérifiez la définition créée, puis validez son intérêt sur les requêtes et les données du projet.