Repérer les prédicats qui empêchent l’accès par index

Une requête peut retourner le bon résultat tout en empêchant MariaDB d’exploiter efficacement un index. C’est souvent le cas lorsqu’un filtre transforme chaque valeur d’une colonne avant de la comparer. Vous allez repérer ces prédicats et les réécrire sans changer le jeu de lignes attendu.

Vous avez déjà appris à lire les modes d’accès et les estimations d’un plan avec EXPLAIN. Ici, vous examinez d’abord la forme du prédicat : un index sur une colonne est plus directement exploitable quand la comparaison porte sur cette colonne sans lui appliquer de calcul ou de fonction.

Reconnaître un prédicat sargable

Un prédicat est dit sargable lorsqu’il permet au moteur de rechercher efficacement les valeurs correspondantes à partir d’un index. Par exemple, comparez directement adherent_id à une valeur plutôt que d’appliquer une fonction à cette colonne. Cette propriété ne garantit pas que MariaDB choisira l’index : le plan dépend aussi des statistiques, de la sélectivité et du volume de lignes concerné.

-- Forme moins favorable : une fonction est appliquée à la colonne.
EXPLAIN
SELECT emprunt_id, adherent_id, date_emprunt
FROM emprunts
WHERE YEAR(date_emprunt) = 2025;

-- Forme directe : la colonne est comparée à des bornes.
EXPLAIN
SELECT emprunt_id, adherent_id, date_emprunt
FROM emprunts
WHERE date_emprunt >= '2025-01-01'
  AND date_emprunt <  '2026-01-01';

Comparez type, key et rows dans les deux plans. Selon les données et les index existants, la seconde forme peut permettre un accès par plage sur un index de date.

La condition avec YEAR(date_emprunt) demande d’évaluer l’année pour les lignes examinées. Un index ordinaire sur date_emprunt contient les valeurs de la colonne, pas le résultat de cette fonction. La réécriture exprime la même année comme un intervalle : la borne basse est incluse et la borne haute exclue, ce qui couvre toutes les heures du 31 décembre si la colonne contient aussi une heure.

Éviter les fonctions et conversions sur les colonnes

Le même principe s’applique à d’autres expressions. Une condition telle que DATE(date_emprunt) = '2025-04-12' supprime la partie horaire, mais transforme la colonne. Exprimez plutôt le jour comme une plage semi-ouverte. Cette forme est précise pour un type temporel et évite de supposer que la dernière valeur de la journée est exactement 23:59:59.

-- Variante à éviter pour un index ordinaire sur date_emprunt.
SELECT emprunt_id, adherent_id, date_emprunt
FROM emprunts
WHERE DATE(date_emprunt) = '2025-04-12';

-- Variante qui compare directement la colonne.
SELECT emprunt_id, adherent_id, date_emprunt
FROM emprunts
WHERE date_emprunt >= '2025-04-12 00:00:00'
  AND date_emprunt <  '2025-04-13 00:00:00';

Les deux requêtes sélectionnent les emprunts datés du 12 avril 2025. La seconde rend possible une recherche par plage sur un index de date_emprunt.

Une conversion implicite peut également rendre le prédicat moins prévisible. Si une colonne numérique est comparée à une chaîne, ou une colonne textuelle à une valeur d’un autre type, MariaDB peut devoir convertir les valeurs pour effectuer la comparaison. Passez des paramètres du type correspondant à la colonne et évitez de convertir celle-ci dans la clause WHERE.

Vérifier et consigner la réécriture

Ajoutez les variantes de référence et réécrites dans sql/requetes.sql, avec un commentaire qui précise les paramètres et le résultat attendu. Ajoutez leurs commandes EXPLAIN à la suite des diagnostics existants dans sql/diagnostics.sql. Relevez possible_keys, key et rows sans conclure à partir d’une seule colonne.

Vérifiez aussi que les deux formulations renvoient les mêmes emprunts sur vos données de test. Un plan affichant un balayage complet n’est pas automatiquement mauvais : si la table est petite ou si une grande partie des lignes correspond au filtre, ce choix peut être pertinent. La leçon suivante portera sur la réduction du nombre de lignes et des colonnes traitées par une requête.

À retenir

• Comparez directement une colonne indexée à une valeur ou à des bornes.
• Remplacez les fonctions temporelles sur la colonne par des plages semi-ouvertes.
• Fournissez des paramètres du type compatible avec la colonne pour éviter les conversions implicites.
• Utilisez EXPLAIN pour comparer les plans, puis vérifiez que les résultats restent équivalents.
• Consignez les requêtes et les observations dans sql/requetes.sql et sql/diagnostics.sql.