MySQL : index, recherches et performance
Lorsqu’on débute avec MySQL ou MariaDB, on se concentre d’abord sur l’écriture de requêtes qui fonctionnent. Mais dès que la base grandit, certaines requêtes deviennent lentes, sans qu’on comprenne toujours pourquoi. Est-ce le volume de données ? Le code PHP ? Le serveur ? Ou simplement une mauvaise structure ?
Dans cet article, nous poursuivons notre série consacrée aux bases relationnelles, en nous concentrant cette fois sur les leviers de performance accessibles à tout développeur : les index, les méthodes de recherche, la structure des tables, et les outils de diagnostic simples. À travers des requêtes concrètes et des visualisations dans PHPMyAdmin, nous verrons comment rendre nos bases plus efficaces, sans les transformer en usine à gaz, ni tomber dans la micro-optimisation. Une approche utile pour tout projet PHP, qu’il s’agisse d’un CMS, d’un back-office, ou d’un outil développé sur mesure.
Quand une base commence à ralentir…
Toutes les requêtes SQL ne se valent pas. Une requête qui met 0,01 seconde sur une base de test peut mettre 5 secondes en production, dès que les données se multiplient. Et dans un projet PHP, ce simple ralentissement peut se transformer en blocage AJAX, en attente côté utilisateur ou en timeout serveur.
Face à ces lenteurs, plusieurs leviers s’offrent à nous : réduire le volume traité, structurer mieux les données, limiter les requêtes imbriquées, ou encore aider le moteur SQL à chercher plus vite. C’est ici que les index entrent en jeu. Ils permettent d’accélérer drastiquement certaines requêtes, à condition de bien comprendre comment ils fonctionnent et sur quels champs ils sont vraiment utiles.
Pourquoi indexer ? Et quels champs viser ?
Un index, en base de données, fonctionne comme la table des matières d’un livre : il permet de retrouver une information rapidement sans avoir à parcourir chaque page. Sans index, une requête de type SELECT * FROM utilisateurs WHERE email = 'x@exemple.com' oblige MySQL à examiner chaque ligne de la table. Sur 20 lignes, ce n’est pas gênant. Sur 20 000, cela commence à ralentir. Sur 200 000, cela devient un problème.
Heureusement, créer un index est simple. Depuis PHPMyAdmin, il suffit d’éditer la structure d’une table et de cliquer sur « Index » en face d’un champ.
En SQL, la commande est tout aussi lisible :
ALTER TABLE utilisateurs ADD INDEX idx_email (email);Cet index permet à MySQL d’atteindre directement les lignes concernées. Le gain est souvent immédiat, surtout sur les colonnes qui servent de filtre (WHERE), de critère de tri (ORDER BY), ou de lien (JOIN).
Mais tout champ ne mérite pas un index. Si une colonne contient très peu de valeurs distinctes (ex. un champ statut avec juste « actif » ou « inactif »), l’index sera peu utile. De même, indexer toutes les colonnes alourdit les insertions, les mises à jour, et occupe de l’espace disque. Un bon réflexe : analyser les requêtes les plus fréquentes de l’application. Quelles colonnes sont filtrées ou triées dans les interfaces PHP ou les appels AJAX ? Sur ces colonnes-là, l’index est rarement superflus.
Pour mieux comprendre ce que cela change concrètement, nous avons mis en ligne une page de test comparatif : Comparer une requête avec ou sans index Vous pouvez y sélectionner un email à rechercher, visualiser les temps de réponse, et observer ce que MySQL fait en coulisses grâce à l’analyse EXPLAIN.
Utiliser un index dans une requête : faut-il le préciser ?
Créer un index est une première étape. Mais encore faut-il que MySQL le reconnaisse et le mobilise. Dans la plupart des cas, le moteur détecte automatiquement les index pertinents. Il s’en sert alors pour accélérer la recherche, le tri, ou les jointures. Mais il arrive qu’il se trompe, ou qu’il les ignore faute d’indications claires.
Heureusement, il est possible d’influencer ce comportement. En ajoutant USE INDEX (nom_index) dans une requête, on peut suggérer à MySQL quel index utiliser. Cela devient utile si plusieurs index existent sur la même table, ou si le moteur opte pour une lecture complète alors qu’un index bien conçu suffirait.
SELECT id, email FROM users USE INDEX (idx_email) WHERE email = 'jean@example.com';Encore faut-il connaître le nom de l’index. Si on l’a créé soi-même en SQL, pas de surprise : on l’a nommé explicitement. Mais si l’index a été ajouté depuis PHPMyAdmin, le nom est souvent généré automatiquement (email, email_2, etc.). On peut le retrouver facilement dans l’onglet « Structure » de la table, ici on voit qu’il s’agit de idx_email.
ou via une requête SQL :
SHOW INDEXES FROM table_choisie;
Dans tous les cas, l’option USE INDEX ne force pas le moteur, mais l’oriente. Pour imposer un choix, on peut aller plus loin avec FORCE INDEX, mais cela doit rester exceptionnel. Le plus souvent, bien écrire sa requête, bien nommer ses index, et analyser avec EXPLAIN suffit à guider MySQL dans la bonne direction.
LIKE ou FULLTEXT ? Choisir la bonne méthode de recherche
Quand on veut retrouver un mot ou une portion de texte dans une base, la solution immédiate qui vient à l’esprit est d’utiliser LIKE, avec des jokers (%). C’est simple, intuitif, et utilisable partout. Mais cette méthode a une contrepartie directe : MySQL doit lire toutes les lignes, sans pouvoir s’appuyer sur un index, surtout si le joker est placé au début (LIKE '%mot%').
Sur une table de quelques centaines de lignes, cela passe. Mais à partir de quelques milliers, la requête devient nettement plus lente. Le moteur parcourt ligne après ligne, et compare chaque valeur. Si cette requête est appelée régulièrement (dans un moteur de recherche, un filtre AJAX, une API…), la charge s’accumule.
Pour éviter cela, MySQL propose un autre mécanisme, plus adapté aux contenus textuels : l’index FULLTEXT.
Utiliser FULLTEXT : ce qu’il faut savoir
FULLTEXT permet de créer un index spécialisé pour les mots, et non pour les valeurs exactes. Il permet des recherches plus rapides dans des champs VARCHAR ou TEXT, y compris sur plusieurs colonnes en même temps.
Mais il ne suffit pas de remplacer la requête. Il faut préparer la base, comme pour un index classique. Et il y a quelques conditions à respecter :
- Seuls les champs de type
CHAR,VARCHARouTEXTpeuvent être indexés ainsi. - Le moteur de la table doit être compatible (MyISAM ou InnoDB, selon les versions).
- Et surtout : il faut créer explicitement l’index
FULLTEXT, il n’est jamais automatique.
Mise en place (SQL ou PHPMyAdmin)
Dans PHPMyAdmin, il suffit d’aller dans l’onglet Structure, de cocher le ou les champs concernés, puis de choisir Index FULLTEXT (Texte entier) dans le menu déroulant, ou dans les choix de lignes.
En SQL, la commande est simple, et vous pouvez également indexer plusieurs colonnes à la fois :
ALTER TABLE articles ADD FULLTEXT (titre);
ALTER TABLE articles ADD FULLTEXT (titre, contenu);Syntaxe de recherche
Une fois l’index en place, on ne passe plus par LIKE, mais par MATCH ... AGAINST :
-- Requête utilisant LIKE : simple mais peu performante
SELECT * FROM articles
WHERE titre LIKE '%solaire%';
-- Requête utilisant FULLTEXT : rapide, mais nécessite un index adapté
SELECT * FROM articles
WHERE MATCH(titre) AGAINST('solaire');Cette requête demande au moteur de trouver les lignes où le mot “solaire” apparaît dans le champ titre, en utilisant l’index FULLTEXT. Le résultat est trié par pertinence, et s’exécute beaucoup plus rapidement qu’un LIKE '%solaire%'.
Quand choisir l’un ou l’autre ?
- Utilisez
LIKEpour des cas ponctuels, sur des champs courts, ou lorsque l’indexation textuelle ne se justifie pas. - Préférez
FULLTEXTsi vous devez chercher régulièrement des mots dans des champs longs (titres, descriptions, textes), et que la table est vouée à croître.
Il ne s’agit pas de remplacer LIKE partout, mais de choisir en connaissance de cause la méthode la plus adaptée à la structure de vos données.
Options avancées de FULLTEXT : affiner ses recherches
Une fois un index FULLTEXT en place, il est possible d’aller plus loin que la simple recherche par mot-clé. MySQL permet d’ajuster finement le comportement de MATCH ... AGAINST en utilisant des modificateurs de recherche, notamment grâce au mode booléen (IN BOOLEAN MODE). Voici quelques exemples pratiques pour mieux comprendre ce que cela permet :
Ajouter ou exclure des mots
Le mode booléen permet de préciser ce que l’expression que l’on souhaite retrouver, mais aussi ce que l’on souhaite exclure des résultats. En ajoutant un + devant un mot, on le rend obligatoire. À l’inverse, un - interdit sa présence. Cela permet de filtrer finement les correspondances dans un texte, en évitant des résultats trop vagues. Dans l’exemple ci-dessous, seules les lignes contenant le mot “solaire” mais pas le mot “photovoltaïque” seront retenues. Ce filtrage logique est très utile lorsqu’une recherche génère trop de bruit.
SELECT * FROM articles
WHERE MATCH(titre) AGAINST('+solaire -photovoltaïque' IN BOOLEAN MODE);Utiliser un joker partiel (fin de mot)
Contrairement à LIKE, qui autorise des jokers n’importe où, la recherche FULLTEXT en mode booléen permet d’utiliser * uniquement en fin de mot. Cela permet de retrouver toutes les variantes commençant par une racine commune, sans avoir à écrire chaque mot possible. La requête qui suit retournera des titres contenant “solaire”, “soleil, …”, ou toute autre forme commençant par “sol”. C’est utile pour rattraper des flexions, des pluriels ou des suffixes fréquents, sans alourdir la requête avec des listes manuelles.
SELECT * FROM articles
WHERE MATCH(titre) AGAINST('sol*' IN BOOLEAN MODE);Pondérer les mots importants
On peut aussi ajuster l’importance relative de certains mots dans la recherche. Le symbole > permet d’augmenter leur poids dans le calcul de pertinence : leur présence améliore le classement du résultat, sans pour autant être obligatoire. À l’inverse, le symbole < diminue leur influence : les résultats contenant ce mot seront légèrement déclassés, mais pas exclus. Cela permet de hiérarchiser les correspondances de manière plus fine. Dans l’exemple suivant, “solaire” et “énergie” sont requis. Le mot “durable”, s’il est présent, renforce la pertinence. On pourrait aussi ajouter <marketing pour signaler que ce mot est secondaire, voire légèrement défavorable.
SELECT * FROM articles
WHERE MATCH(titre) AGAINST('+solaire +énergie >durable <marketing' IN BOOLEAN MODE);Étendre la recherche avec WITH QUERY EXPANSION
Cette option, à ne pas confondre avec le mode booléen, permet à MySQL de relancer une recherche élargie à partir des résultats initiaux. Elle peut être utile quand un terme est trop restreint ou ambigu. Le moteur identifie alors d’autres mots courants dans les premiers résultats, puis élargit la recherche en les prenant en compte. Le principe est séduisant, mais il faut rester prudent : les résultats deviennent moins prévisibles, et la cohérence des correspondances peut varier. Cela reste un outil à réserver à des cas où l’objectif est d’élargir l’intention de recherche, comme dans un moteur exploratoire.
Par exemple, un utilisateur recherche des cours de dessin au fusain. Si ce terme est trop précis et n’aboutit qu’à peu de résultats, MySQL peut, grâce à WITH QUERY EXPANSION, analyser les premiers contenus trouvés et en extraire des termes fréquemment associés — comme croquis, charbon, ou techniques artistiques. Il relance alors une requête élargie en tenant compte de ces nouveaux éléments. Ce mécanisme, automatisé et implicite, peut être utile quand l’internaute ne sait pas formuler exactement ce qu’il cherche, ou lorsqu’on souhaite encourager une recherche plus souple, laissant place à un élargissement naturel autour de l’idée initiale.
SELECT * FROM articles
WHERE MATCH(titre) AGAINST('dessin fusain' WITH QUERY EXPANSION);Ce qu’il faut savoir
Le mode booléen offre un contrôle précis sur la recherche textuelle : il permet d’imposer certains mots, d’en exclure d’autres, d’utiliser des variantes partielles ou de pondérer la pertinence. Ces syntaxes ne sont disponibles que si un index FULLTEXT est actif, mais elles s’avèrent très utiles dans le cadre d’un moteur de recherche intégré ou d’un système de filtrage avancé dans une interface web.
Types, tailles et structures : quand la forme ralentit le fond
Un index n’est pas magique : il s’appuie sur la structure des données. Si celle-ci est mal pensée, même un bon index peut devenir inefficace. Le type de champ, sa taille, son encodage, et même l’ordre des colonnes dans une table peuvent influencer fortement la performance.
Par exemple, indexer un champ VARCHAR(500) est non seulement lourd, mais rarement pertinent. Plus un champ est long, plus l’indexation est coûteuse, car MySQL doit comparer plus de données à chaque requête. Dans bien des cas, une taille raisonnable suffit : par exemple un VARCHAR(100) est souvent préférable pour un champ email, surtout si l’on sait que les adresses ne dépasseront jamais 80 caractères (voir Limits on Table Column Count and Row Size).
Autre facteur : l’encodage. UTF-8 (ou plutôt utf8mb4, What is the difference between utf8mb4 and utf8 charsets in MySQL?) permet une compatibilité maximale avec les langues du monde entier, mais chaque caractère peut occuper jusqu’à 4 octets. Sur une base multilingue ou des recherches textuelles, cela peut alourdir les index. Pour certains usages internes, un encodage plus léger comme latin1 peut encore se justifier, à condition d’en comprendre les limites (voir Character Sets and Collations).
Enfin, il est important de repenser les colonnes rarement utilisées. Un champ description de type TEXT, s’il n’est jamais filtré ou trié, n’a aucun intérêt à être indexé. Il doit même être séparé logiquement si la table devient trop lourde. Dans les interfaces web, il est parfois plus judicieux de créer une table dédiée aux métadonnées longues plutôt que d’alourdir systématiquement la structure principale (voir What’s the better database design: more tables or more columns?).
MyISAM ou InnoDB ? Ce que ça change pour les index et FULLTEXT
Durant plusieurs années, MyISAM était le seul moteur de MySQL à proposer l’indexation FULLTEXT (voir InnoDB vs MyISAM, et Les Principaux Moteurs de Stockage pour MySQL), ce qui l’a rendu populaire pour les systèmes de recherche textuelle. Rapide en lecture, simple à manipuler, il avait cependant de sérieuses limites : aucune gestion des relations (FOREIGN KEY), pas de transactions, ni de contrôle d’intégrité en cas de plantage.
Depuis MySQL 5.6 (et MariaDB 10.0), InnoDB permet lui aussi les index FULLTEXT, tout en conservant ses avantages : gestion des relations, des transactions, des verrous fins et d’une meilleure robustesse globale. Cela change la donne, car on peut désormais utiliser la recherche FULLTEXT sans renoncer à la logique relationnelle.
Pour autant, les deux moteurs ne se comportent pas exactement de la même façon. MyISAM reste plus rapide sur certains jeux de données en lecture pure (voir FULLTEXT indexes in MAriaDB 10), mais dès qu’on écrit ou modifie beaucoup, InnoDB offre une bien meilleure stabilité. La syntaxe de création d’index FULLTEXT reste identique dans les deux cas, et rien ne change côté MATCH ... AGAINST.
En résumé, InnoDB est aujourd’hui le moteur par défaut et recommandé. Il prend en charge toutes les fonctions modernes de MySQL, y compris FULLTEXT. MyISAM peut encore servir pour des usages très spécifiques, mais n’est plus conseillé pour des projets évolutifs ou critiques.
Diagnostiquer et optimiser plus largement
Un index bien placé ne fait pas tout. Il faut parfois aller plus loin pour comprendre pourquoi une requête ralentit, ou pourquoi MySQL ne semble pas utiliser les index qu’on a pourtant soigneusement créés. Heureusement, MySQL et MariaDB offrent plusieurs outils pour diagnostiquer les problèmes, souvent sans rien installer de plus.
Optimiser une requête ne tient pas à une formule magique, mais à une combinaison de détails. Un SELECT précis évite de charger des colonnes inutiles. Un LIMIT bien placé réduit l’attente perçue, surtout dans une interface web. Et un EXPLAIN montre vite si l’index est réellement utilisé. Le journal des requêtes lentes (slow_query_log) met en évidence les instructions les plus coûteuses à corriger. Et dans certains cas, un cache actif (global ou par table) peut accélérer fortement l’affichage de requêtes répétitives, à condition qu’elles ne changent pas à chaque appel.
Limiter les requêtes trop larges
Un SELECT * charge toutes les colonnes, y compris celles dont vous n’avez pas besoin. Cela alourdit le trafic entre la base et le serveur web, mobilise plus de mémoire, et complique l’optimisation par le moteur. Si vous n’avez besoin que de id, nom et email, mieux vaut les demander explicitement.
-- Moins efficace :
SELECT * FROM inscriptions WHERE date > '2025-01-01';
-- Plus rapide et plus clair :
SELECT id, nom, email FROM inscriptions WHERE date > '2025-01-01';Sur une table de 20 colonnes, cette simple précision peut réduire de moitié la quantité de données transférées à chaque appel.
Utiliser LIMIT intelligemment
Un LIMIT 50 suffit souvent dans une interface paginée. Mais ce n’est pas magique : si aucun index ne guide le tri, MySQL devra d’abord classer toutes les lignes avant de tronquer. L’exemple ci-dessous montre l’impact d’un index explicite.
-- Trie toutes les lignes, puis coupe :
SELECT * FROM logs ORDER BY date DESC LIMIT 50;
-- Utilise un index pour lire uniquement les 50 dernières :
SELECT id, message FROM logs USE INDEX (idx_date) ORDER BY date DESC LIMIT 50;Lorsque l’index idx_date est bien présent, le gain est immédiat, surtout sur de grandes tables.
Comprendre le plan d’exécution avec EXPLAIN
Avant d’optimiser une requête, il faut savoir comment elle est interprétée. EXPLAIN montre le chemin prévu par le moteur : index utilisé, type d’accès (ALL, ref, const…), estimation du nombre de lignes, création d’une table temporaire…
EXPLAIN SELECT * FROM users WHERE email = 'jean@example.com';Si la colonne email est indexée, on s’attend à voir type = ref ou const dans la colonne type, et un faible nombre de lignes estimées. Mais si l’on voit ALL, c’est que l’index n’est pas utilisé, souvent à cause d’un mauvais encodage, d’une clause mal formulée ou d’un index absent.
Un plan EXPLAIN indique le chemin logique prévu par MySQL : type d’accès (ALL, ref, range…), index sélectionné (key), nombre de lignes estimées (rows), et indication éventuelle d’une table temporaire. À chaque anomalie visible, il faut se poser la question : cet index est-il pertinent ? cette clause est-elle écrite de façon exploitable ?. Voir le détail sur notre Comparateur d’accès avec ou sans index.
Le journal des requêtes lentes (slow_query_log)
Un plan EXPLAIN reste théorique. Pour détecter ce qui ralentit réellement l’application, activez le slow_query_log. Il enregistre les requêtes dépassant un seuil défini (par exemple 2 secondes). Une requête comme celle-ci, avec une fonction qui empêche l’usage d’un index, y apparaîtra :
-- Activer temporairement le journal des requêtes lentes :
SET GLOBAL slow_query_log = 1;
SET GLOBAL long_query_time = 2;
-- exécution de la requete qui peut etre problématique
SELECT * FROM users WHERE LEFT(name, 3) = 'Mar';Ici, la fonction LEFT() empêche MySQL d’utiliser l’index pourtant défini sur name. Le journal permet de repérer ce type de blocage. On peut alors réécrire la requête de façon plus efficace :
-- Forme optimisable, exploitant un index sur name :
SELECT * FROM users USE INDEX (idx_name) WHERE name LIKE 'Mar%';Le slow_query_log peut aussi être activé de manière durable dans my.cnf (slow_query_log = 1). C’est un excellent outil pour cibler les ralentissements réels, et non supposés.
Le cache des requêtes
Certaines versions de MariaDB permettent encore d’activer un cache global (query_cache_size, query_cache_type), tandis que MySQL l’a abandonné au profit d’une gestion plus fine directement intégrée à InnoDB. Dans tous les cas, lorsqu’une requête strictement identique est rejouée rapidement, elle peut bénéficier d’une exécution accélérée grâce à la réutilisation de son résultat.
Mais ce cache repose sur une comparaison textuelle de la requête : une variation minime dans les valeurs, comme email = 'a@x.com' puis email = 'b@x.com', suffit à invalider le cache. Il est donc principalement utile pour des contenus peu personnalisés ou récurrents, comme des listes publiques, des tableaux de bord ou des pages consultées de manière identique par de nombreux utilisateurs.
Conclusion : une question d’équilibre… et de lucidité
Optimiser les performances dans MySQL ou MariaDB ne se résume pas à cocher des cases. C’est un équilibre entre structure, usage, volume, et logique applicative. Un index bien pensé peut transformer une requête lourde en réponse instantanée, mais il doit correspondre aux vrais besoins de lecture. À l’inverse, sur-optimiser trop tôt ou indexer à l’aveugle peut ralentir les écritures et rendre la base inutilement complexe.
Nous avons vu qu’un simple LIKE '%mot%' peut poser problème, qu’un EXPLAIN révèle bien des surprises, et qu’un USE INDEX bien placé peut soulager toute une API. Mais tout cela ne remplace pas une vraie observation du terrain : logs, journal des requêtes lentes, et tests en conditions réelles sont nos meilleurs alliés.
L’objectif n’est pas d’obtenir un score parfait, mais de garder un site fluide, stable, évolutif. Il n’y a pas de recette unique. Chaque projet, chaque table, chaque requête mérite son regard propre. Et parfois, le plus grand gain vient simplement d’une colonne qu’on n’utilise jamais… qu’on supprime enfin.
