Pourquoi vos index MySQL sont ignorés en silence par Eloquent

Vous avez ajouté l'index. Vous l'avez vérifié avec SHOW INDEX FROM orders. Et pourtant, votre requête met toujours plusieurs secondes à s'exécuter en production. Le dashboard tourne, les utilisateurs attendent, et MySQL parcourt 4 millions de lignes comme si l'index n'existait pas.

Ce scénario est plus courant qu'on ne le pense, et il illustre parfaitement ce que l'on appelle la dette technique invisible : un problème silencieux, sans erreur dans les logs, sans avertissement d'Eloquent, mais qui dégrade progressivement les performances de votre application.

La cause racine porte un nom : la sargabilité.

Comprendre la sargabilité : le concept que tout développeur PHP devrait connaître

Un prédicat est dit sargable (Search ARGument ABLE) lorsque MySQL peut utiliser un index pour le résoudre directement. C'est la condition sine qua non pour qu'un index B-tree soit réellement utilisé par l'optimiseur de requêtes.

Le problème survient dès qu'une fonction enveloppe la colonne indexée. À ce moment-là, MySQL ne peut plus se servir de l'index : il doit calculer cette fonction pour chaque ligne de la table, puis filtrer les résultats. Résultat : un full table scan, quelle que soit la qualité de votre indexation.

Eloquent propose plusieurs méthodes pratiques qui déclenchent ce comportement silencieusement. En voici les plus courantes.

Les patterns Eloquent qui cassent vos index

1. whereDate(), whereMonth(), whereYear()

Ces helpers semblent anodins, mais ils enveloppent la colonne dans une fonction SQL :

// ❌ Non-sargable : génère DATE(created_at) = ?
Order::whereDate('created_at', today());

// ✅ Sargable : utilise une plage sur la colonne brute
Order::whereBetween('created_at', [
    today()->startOfDay(),
    today()->endOfDay(),
]);

La version corrigée laisse la colonne created_at intacte et définit une plage que l'index peut parcourir directement.

2. Fonctions sur colonnes dans where()

Appliquer YEAR(), MONTH(), DAY() ou toute autre fonction SQL directement dans un where() produit le même effet :

// ❌ Full scan garanti
Order::whereRaw('YEAR(created_at) = ?', [2024]);

// ✅ Plage explicite
Order::whereBetween('created_at', [
    Carbon::create(2024, 1, 1)->startOfDay(),
    Carbon::create(2024, 12, 31)->endOfDay(),
]);

3. LIKE avec wildcard en préfixe

Un LIKE '%terme' ou LIKE '%terme%' force un scan complet. L'index ne peut être utilisé que si le wildcard est en suffixe :

// ❌ Ignore l'index
User::where('email', 'LIKE', '%@mulertech.com');

// ✅ Utilise l'index (wildcard en fin)
User::where('email', 'LIKE', 'contact%');

Pour les recherches full-text complexes, envisagez un moteur dédié comme Elasticsearch ou Meilisearch plutôt que de lutter contre les limites de MySQL.

4. Casts et conversions implicites

Lorsque le type PHP ne correspond pas au type de la colonne MySQL, l'optimiseur peut être forcé de convertir chaque valeur avant comparaison :

// ❌ Si `user_id` est un INT et que vous passez une string
Order::where('user_id', '123'); // risque de conversion implicite

// ✅ Type explicite
Order::where('user_id', (int) $userId);

Ce cas est particulièrement sournois car Eloquent ne lève aucune exception, et le comportement peut varier selon la version de MySQL.

5. Colonnes calculées dans orderBy()

Un ORDER BY sur une expression calculée empêche l'utilisation d'un index pour le tri :

// ❌ Calcul à la volée, tri en mémoire
Order::orderByRaw('YEAR(created_at) DESC');

// ✅ Tri sur la colonne brute
Order::orderBy('created_at', 'DESC');

6. Nullable columns et comparaisons

Comparer une colonne nullable avec != null via whereRaw peut produire des comportements inattendus et contourner les index. Préférez toujours les méthodes dédiées :

// ❌ Comportement non optimal
Order::whereRaw('deleted_at != NULL');

// ✅ Sémantique claire, index utilisé
Order::whereNotNull('deleted_at');

Comment diagnostiquer avec EXPLAIN

Avant toute optimisation, utilisez EXPLAIN pour vérifier ce que MySQL fait réellement :

$query = Order::whereDate('created_at', today());

dd(
    $query->toSql(),
    DB::select('EXPLAIN ' . $query->toSql(), $query->getBindings())
);

Dans le résultat, concentrez-vous sur deux colonnes :

• type : la valeur ALL signifie un full table scan. Vous cherchez range, ref ou eq_ref.
• key : indique quel index a été utilisé. NULL confirme que l'index est ignoré.

Cette vérification prend moins d'une minute et peut vous éviter des heures de débogage en production.

La dette technique invisible : un enjeu de maintenabilité

Ce qui rend ces patterns particulièrement dangereux, c'est leur invisibilité à court terme. En développement, sur quelques centaines de lignes, whereDate() fonctionne parfaitement. C'est en production, quand la table dépasse le million de lignes, que le problème se manifeste — souvent lors d'un pic de charge, rarement dans un contexte facile à déboguer.

C'est la définition même de la dette technique invisible : des choix apparemment neutres qui accumulent un coût différé, difficile à tracer jusqu'à sa source.

La bonne pratique chez MulerTech consiste à intégrer ces vérifications dès la phase de développement :

• Activer le query log pendant les revues de code sur les parties critiques
• Systématiser EXPLAIN sur toutes les requêtes impliquant de grandes tables
• Documenter les colonnes indexées et les contraintes qui s'y appliquent

Conclusion

Ajouter un index ne suffit pas : encore faut-il écrire des requêtes que MySQL peut en tirer parti. Les helpers Eloquent comme whereDate() ou les whereRaw() avec fonctions sur colonnes sont pratiques, mais ils sacrifient silencieusement la sargabilité.

Prendre l'habitude de vérifier ses requêtes avec EXPLAIN et de privilégier les plages explicites sur les fonctions de colonne, c'est un investissement modeste pour des gains de performance significatifs — et une façon concrète de limiter l'accumulation de dette technique.

Cet article s'inspire de 6 Eloquent Patterns That Silently Break MySQL Index Usage publié par Hafiz sur DEV Community.