Quand les index ne se déclenchent pas — Motifs et solutions

Motifs où un index existe mais ne se déclenche pas

Même après que tu as créé un index, certaines manières d'écrire la condition l'empêchent d'être utilisé et la requête tourne quand même en parcours complet de table (SCAN).

Les trois coupables classiques sont ci-dessous.

• Conditions qui enveloppent la colonne dans une fonction ou de l'arithmétique (substr(col) / upper(col) / col + 0, et ainsi de suite)
• LIKE avec correspondance au milieu LIKE '%x%'
• Conditions de négation (<> / NOT IN)

Dans chaque cas la cause est la même : la comparaison ne s'aligne plus avec l'ordre stocké dans l'index.

Plus loin dans l'article, nous regardons les index d'expression pour les cas où tu ne peux pas éviter d'envelopper la colonne dans une fonction, et les index partiels pour restreindre l'index à un sous-ensemble spécifique de lignes.

Tout au long de cet article, tu vas comparer « la façon qui n'utilise pas l'index » avec « la façon qui l'utilise » côte à côte dans le plan de requête.

-- Même avec un index sur amount,
-- envelopper la colonne dans une fonction retombe sur un parcours complet de table
DROP INDEX IF EXISTS ix_demo;
CREATE INDEX ix_demo ON perf_sales(amount);

-- Compare la colonne telle quelle → SEARCH
EXPLAIN QUERY PLAN
SELECT COUNT(*) FROM perf_sales WHERE amount >= 800000;

-- Envelopper la colonne dans une fonction → retour à SCAN
EXPLAIN QUERY PLAN
SELECT COUNT(*) FROM perf_sales WHERE abs(amount) >= 800000;

Considère l'exigence : « Je veux compter uniquement les ventes de 2024. » Crée un index sur sale_date, puis compare deux plans de requête côte à côte — un qui enveloppe la colonne dans une fonction, et un qui utilise une condition de plage avec la colonne telle quelle — et regarde comment SCAN vs SEARCH change. Reconstruis l'index dans cette seule exécution de console pour que le plan soit totalement autonome. (Exécute-le correctement pour révéler l'explication.)

① Supprime tout index existant avec DROP INDEX IF EXISTS et crée un index à colonne unique sur sale_date.

② Utilise EXPLAIN QUERY PLAN sur une requête qui extrait l'année de sale_date avec substr et la compare à '2024' (la forme qui enveloppe la colonne dans une fonction).

③ Puis montre le plan pour une requête qui exprime le même sens sous forme de condition de plage sur sale_date (supérieur ou égal au début de 2024, inférieur au début de l'année suivante) et compare-le avec ②.

LIKE avec correspondance au milieu

Un LIKE avec correspondance au préfixe (une forme où le début est fixé, comme 'P00%') peut en principe restreindre une plage en utilisant l'ordre de l'index.

À l'inverse, une correspondance au milieu ('%5%') ou une correspondance au suffixe ('%50') n'a pas de début fixé, donc aucune base de données ne peut parcourir l'index et la requête devient un parcours complet de table.

Cela dit, en SQLite, `LIKE` est insensible à la casse par défaut, donc il ne s'aligne pas avec l'index ordonné en BINARY habituel — même un LIKE avec correspondance au préfixe n'obtient pas un SEARCH et retombe sur SCAN (voir le callout ci-dessous).

Donc quand tu veux qu'une correspondance au préfixe en SQLite utilise une recherche par index, la solution directe est de la réécrire en condition de plage, comme product >= 'P00' AND product < 'P01'.

Pour le LIKE avec correspondance au milieu, si tu en as vraiment besoin comme exigence, considère un mécanisme différent comme la recherche full-text.

Le LIKE de SQLite est insensible à la casse par défaut

En SQLite, PRAGMA case_sensitive_like est OFF par défaut, donc LIKE est une comparaison insensible à la casse.

Les index sont construits en utilisant l'ordre BINARY standard (ordre de dictionnaire sensible à la casse), donc même une correspondance au préfixe comme LIKE 'P00%' ne s'aligne pas avec l'ordre de l'index et la règle de comparaison — elle n'est pas utilisée comme un SEARCH.

Si tu exécutes LIKE 'P00%' dans cette console, le plan devient SCAN perf_sales USING COVERING INDEX.

Les contournements directs sont de la réécrire en condition de plage (product >= 'P00' AND product < 'P01'), ou d'énumérer les variantes de casse côté valeur (product IN ('P050', 'p050')).

Dans cet article nous choisissons l'option la plus directe — la réécrire en condition de plage.

En MySQL / PostgreSQL, si LIKE est configuré pour être sensible à la casse, une correspondance au préfixe devient en général un SEARCH.

-- Compare les plans condition de plage et LIKE contre un index sur product
DROP INDEX IF EXISTS ix_demo;
CREATE INDEX ix_demo ON perf_sales(product);

-- Condition de plage → début fixé, donc SEARCH
EXPLAIN QUERY PLAN
SELECT COUNT(*) FROM perf_sales
WHERE product >= 'P15' AND product < 'P16';

-- LIKE correspondance préfixe → SCAN en SQLite (insensible à la casse)
EXPLAIN QUERY PLAN
SELECT COUNT(*) FROM perf_sales WHERE product LIKE 'P15%';

-- LIKE correspondance milieu → SCAN dans n'importe quelle base
EXPLAIN QUERY PLAN
SELECT COUNT(*) FROM perf_sales WHERE product LIKE '%9%';

Prends l'exigence « Je veux compter les ventes dont le code produit commence par un préfixe particulier » et compare les plans pour trois formes : (A) une condition de plage, (B) un LIKE avec correspondance au préfixe, et (C) un LIKE avec correspondance au milieu. Crée un index sur product et regarde quelle forme devient un SEARCH en SQLite.

① Supprime tout index existant avec DROP INDEX IF EXISTS et crée un index à colonne unique sur product.

② Utilise EXPLAIN QUERY PLAN sur une requête qui compte les lignes avec la condition de plage WHERE product >= 'P00' AND product < 'P01'.

③ Montre le plan pour le même sens écrit comme un LIKE avec correspondance au préfixe 'P00%' et compare-le avec ②.

④ Puis montre aussi le plan pour un LIKE avec correspondance au milieu '%5%'.

Envelopper la colonne avec upper() ou de l'arithmétique empêche l'index d'être utilisé

Quand tu appliques une fonction ou de l'arithmétique à une colonne pour la filtrer, l'index de la colonne ne peut pas être utilisé même s'il en existe un.

La raison est que l'index est ordonné par les valeurs brutes de la colonne, donc une comparaison contre une valeur transformée comme upper(product) ou amount + 10000 ne s'aligne plus avec l'ordre de l'index.

Par exemple, WHERE upper(product) = 'P050' doit pousser le product de chaque ligne à travers upper() avant de comparer, donc l'index sur product n'est pas utilisé et la requête retombe sur un parcours complet.

De même, WHERE amount + 10000 = 900000 doit calculer amount + 10000 pour chaque ligne avant de comparer, donc l'index sur amount n'est pas utilisé.

La solution est de laisser la colonne tranquille et d'ajuster côté valeur à la place.

upper(product) = 'P050', si les seules valeurs attendues sont 'P050' et 'p050', devient WHERE product IN ('P050', 'p050') — et IN permet à l'index de restreindre plusieurs valeurs d'un coup.

amount + 10000 = 900000 peut être réécrit directement en amount = 800000 en soustrayant 10000 des deux côtés, et l'index sur amount le restreint en une seule fois.

Réécrire substr(sale_date, 1, 4) en condition de plage dans la section précédente est la même idée de « ne pas transformer la colonne » appliquée aux dates.

-- Exemple d'enveloppement de la colonne avec lower() contre un index sur product
DROP INDEX IF EXISTS ix_demo;
CREATE INDEX ix_demo ON perf_sales(product);

-- Envelopper la colonne avec lower() → SCAN
EXPLAIN QUERY PLAN
SELECT COUNT(*) FROM perf_sales WHERE lower(product) = 'p001';

-- Énumérer les variantes de casse côté valeur → SEARCH
EXPLAIN QUERY PLAN
SELECT COUNT(*) FROM perf_sales WHERE product IN ('P001', 'p001');

-- Exemple d'enveloppement arithmétique (contre un index sur qty)
DROP INDEX IF EXISTS ix_qty;
CREATE INDEX ix_qty ON perf_sales(qty);

-- Envelopper avec qty * 2 → SCAN
EXPLAIN QUERY PLAN
SELECT COUNT(*) FROM perf_sales WHERE qty * 2 = 20;

-- Simplifier l'expression côté valeur (diviser les deux côtés par 2) → SEARCH
EXPLAIN QUERY PLAN
SELECT COUNT(*) FROM perf_sales WHERE qty = 10;

Considère l'exigence : « Je veux compter les ventes pour le code produit 'P050' (ou sa forme en minuscules 'p050'). »

Crée un index sur product et compare les plans pour deux formes — envelopper la colonne avec upper() versus énumérer les valeurs candidates avec IN (...) côté valeur.

Les deux formes ciblent la même comparaison couvrant 'P050' et 'p050', donc le résultat est identique.

① Supprime tout index existant avec DROP INDEX IF EXISTS et crée un index à colonne unique sur product.

② Utilise EXPLAIN QUERY PLAN sur une requête qui enveloppe la colonne avec upper() comme dans WHERE upper(product) = 'P050'.

③ Montre le plan pour le même sens écrit comme WHERE product IN ('P050', 'p050') et compare-le avec ②.

Pour l'exigence « Je veux compter les ventes avec un montant d'exactement 800 000 », crée un index sur amount et compare les plans pour deux formes — appliquer de l'arithmétique à la colonne versus ajuster côté valeur.

amount + 10000 = 900000 devient amount = 800000 une fois que tu soustrais 10000 des deux côtés — mathématiquement complètement équivalent.

① Supprime tout index existant avec DROP INDEX IF EXISTS et crée un index à colonne unique sur amount.

② Utilise EXPLAIN QUERY PLAN sur une requête qui applique de l'arithmétique à la colonne comme dans WHERE amount + 10000 = 900000.

③ Montre le plan pour le même sens écrit comme WHERE amount = 800000 et compare-le avec ②.

Conditions de négation (<>, NOT IN)

Les conditions de négation comme <> (différent de) ou NOT IN finissent souvent par cibler « la majeure partie de la table », donc il n'y a aucun intérêt à restreindre avec un index.

Par exemple, quand status est 'paid' (autour de 40 000 sur 50 000 lignes) / 'pending' (autour de 5 000) / 'refunded' (autour de 5 000), WHERE status <> 'paid' cible 10 000 lignes — environ 20 % de la table.

La sélectivité n'est pas assez basse, donc l'optimiseur décide qu'un parcours complet de table est moins cher.

La solution est de réécrire en condition d'égalité.

WHERE status <> 'paid' peut être réécrit comme WHERE status IN ('pending', 'refunded'), et cette forme peut restreindre les lignes cibles via l'index.

-- Compare la réécriture NOT IN vs IN contre un index sur status
DROP INDEX IF EXISTS ix_demo;
CREATE INDEX ix_demo ON perf_sales(status);

-- Condition de négation (NOT IN) → cible large, SCAN
EXPLAIN QUERY PLAN
SELECT COUNT(*) FROM perf_sales WHERE status NOT IN ('refunded');

-- Réécrit en égalité (IN) → SEARCH
EXPLAIN QUERY PLAN
SELECT COUNT(*) FROM perf_sales WHERE status IN ('paid', 'pending');

Considère l'exigence : « Je veux compter les ventes autres que paid. » Crée un index sur status et compare les plans entre l'écrire avec <> et réécrire le même sens en condition d'égalité avec IN.

① Supprime tout index existant avec DROP INDEX IF EXISTS et crée un index à colonne unique sur status.

② Utilise EXPLAIN QUERY PLAN sur une requête qui compte les lignes correspondant à WHERE status <> 'paid'.

③ Montre le plan pour le même sens réécrit comme WHERE status IN ('pending', 'refunded') et compare-le avec ②.

Corrige-le avec des index d'expression et des index partiels

Quand envelopper la colonne dans une fonction est inévitable, un index d'expression (un index construit sur le résultat d'une expression, pas la colonne brute) sauve la situation.

Avec CREATE INDEX ix ON perf_sales(substr(sale_date,1,4)), créer un index sur la même expression que la condition utilise transforme la comparaison sur cette expression en recherche par index.

L'exigence est que l'expression dans la condition corresponde à l'expression dans l'index.

L'autre outil est un index partiel (un index avec une clause WHERE, construit uniquement pour un sous-ensemble spécifique de lignes).

Si les lignes status='pending' ne sont qu'une partie de la table (5 000 sur 50 000), tu peux construire un index restreint comme CREATE INDEX ix ON perf_sales(emp_id) WHERE status='pending'.

L'index lui-même reste petit et restreint efficacement les requêtes qui incluent cette condition.

-- Index d'expression : construis-le sur la même expression que la condition utilise
DROP INDEX IF EXISTS ix_demo;
CREATE INDEX ix_demo ON perf_sales(substr(sale_date, 1, 4));

EXPLAIN QUERY PLAN
SELECT COUNT(*) FROM perf_sales
WHERE substr(sale_date, 1, 4) = '2024';

Considère l'exigence : « Je veux consulter les ventes en cours (pending) restreintes par commercial. » Pending est un petit sous-ensemble (5 000 sur 50 000), donc construis un index partiel qui cible uniquement ces lignes et confirme qu'un plan de requête incluant status='pending' devient une recherche par index.

① Supprime tout index existant avec DROP INDEX IF EXISTS.

② Sur emp_id, crée un index partiel (en utilisant la forme CREATE INDEX ... WHERE ...) ciblant uniquement les lignes où status vaut 'pending'.

③ Utilise EXPLAIN QUERY PLAN sur une requête qui compte les lignes où status est 'pending' et un emp_id spécifique, et confirme que l'index partiel est utilisé.