Alors qu'il semble automatique aux développeurs de penser à l'optimisation du stockage des données dans une base et au choix le plus adapté des types de données, la création d'indexes pertinents passe souvent à la trappe, peut-être parce que leur intérêt prend forme sur des larges tables et est donc indécelable au moment du développement et des premiers tests. Certes, les indexes prennent un peu de place, mais de manière aussi importante (voire plus, avec l'augmentation constante des espaces disque) que la taille même de la base, ses performances sont capitales.

Un index SQL est similaire à un ensemble de marqueurs ou pointeurs, et comparable au classement alphabétique des contacts d'un agenda : si je cherche dans mon agenda le nom d'une personne commençant par "T", je saute immédiatement à l'intercalaire "T" sans parcourir la liste complète de mes contacts.
Les indexes SQL permettent ainsi de gagner un temps précieux en recherche et en tri sur des colonnes ou groupes de colonnes de types numériques ou chaînes de caractères, spécialement sur les requêtes comportant des jointures (JOIN).

Si les clés primaires sont automatiquement indexées, le choix des indexes sur d'autres colonnes revient au concepteur de la BDD. Il convient d'en créer sur toutes les colonnes qu'une application peut vouloir trier (notes données à des produits, titres si l'on veut pouvoir afficher des résultats par ordre alphabétique, dates pour affichages chronologiques, etc.), et particulièrement sur les clés étrangères (en vue des jointures).

On peut noter que les indexes, en accélérant la vitesse de lecture (SELECT) d'une table augmentent sa vitesse d'édition (INSERT, UPDATE), mais généralement de manière peu perceptible et portée sur le back-office. Dans le doute des besoins futurs d'une application, et pour un site majoritairement mis à jour par back-office, ne pas hésiter donc à créer des indexes susceptibles d'être utilisés plus tard.

On crée 2 tables "test1" et "test2" contenant chacune 2 champs "annee" (type INT) et "titre" (type TEXT). La seule différence entre ces 2 tables est que les champs de "test2" sont indexés. On insère dans chacune 100.000 enregistrements (le champ "titre" est alimenté par une chaîne aléatoire md5) :

On obtient sur cet exemple simple des temps de calculs différents d'un facteur ~10 pour une recherche d'entier, et d'un facteur ~80 pour une recherche de texte! Pensons aux différences que cela fera sur une jointure de plusieurs tables...

Les redondances dans une base de données sont généralement assimilées à des erreurs de conception ou des problèmes de modélisation. Elles peuvent parfois pourtant apporter, au prix d'un script de synchronisation maintenant la cohérence des données, une simplification des requêtes (facilitant le développement) et une réelle optimisation des accès en lecture (SELECT), principalement en supprimant la nécessité de certaines jointures (JOIN) et/ou unions (UNION).

Prenons l'exemple d'un site de vidéos, dans lequel chaque vidéo peut être attribuée soit à un pays, soit à un produit, soit à un producteur de vidéos.

• Les pays, produits et producteurs étant des structures de données très différents les unes des autres, et étant liés à des tables distinctes décrivant leurs attributs, nous ne souhaitons pas les regrouper dans une seule table, mais bien créer les 3 tables "pays", "produit" et "producteur".
• De plus chaque producteur est décrit comme appartenant à un pays, on trouvera donc dans sa table une colonne "pays_id".
• Et chaque produit est décrit comme appartenant à un producteur, on trouvera donc dans sa table une colonne "producteur_id".
• Dans la table "video" nous avons un champ enum('pays', 'produit', 'producteur') et un champ "parent_id" permettant de lier chaque vidéo à son possesseur.
• Nous voulons, dans le site, en de nombreux endroits, afficher les vidéos par pays, par produit ou par producteur, étant entendu que les vidéos d'un pays doivent comporter également les vidéos liées aux produits et producteurs de ce pays.

En l'état de cette structure de base, afin d'afficher ou de compter les vidéos d'un pays, on doit donc à chaque fois écrire une requête du type :

Cette requête pouvant comporter des nuances (effectuer un COUNT au lieu d'une sélection de champs, éventuels filtres et tris, etc.) est visiblement désagréable à composer, recopier et adapter, mais aussi relativement lourde à exécuter :
Pourquoi ne pas introduire dans la table vidéo un champ "pays_id"?
Cette requête deviendrait ainsi :

Inconvénients :

• les puristes diront que c'est tout bonnement une mauvaise pratique / «une base redondante est une base mal conçue».
• la nécessité de créer un script qui met à jour le champ "pays_id" de la table "video" lorsque modifié dans la table "producteur".

Avantages :

• simplicité du développement et de la maintenance / re-lecture du code.
• un appel à la base ultra-léger et qui sera exécuté quotidiennement des milliers de fois, à chaque page calculée par chaque visiteur, autant d'économies de ressources.

Présentes dans presque tous les sites web, avec ou sans possibilité supplémentaire de filtrer ou trier les éléments, les listes paginées dans toutes leurs formes sont souvent le rebut du développeur. Error-prone et polymorphe, une liste paginée est de plus facilement fuite de ressources serveur sur les sites présentant beaucoup de contenu. Elle est l'illustration idéale des remarques qui vont suivre, ces remarques s'appliquant néanmoins de manière générique à tout type de conception web comportant une base de données.

Une raison principale de ce gaspillage de ressources est dûe au besoin de connaître le nombre total d'éléments existants pour la requête courante, indispensable à l'affichage de la pagination (boutons "précédent", "suivant", "première page", "dernière page", etc.). Et lorsque notre page propose en plus des filtres simples ou plus complexes, ce nombre total ne peut être mis en cache si les combinaisons offertes à l'utilisateur sont nombreuses : on peut vouloir par exemple afficher les livres d'un revendeur et les filtrer par auteur, par année, par collection, par thème, etc. avec toutes les combinaisons possibles de ces critères ; d'où la nécessité impérieuse d'obtenir ce nombre dynamiquement à l'aide d'une requête SQL quelle qu'elle soit.

Puisque l'on doit également, et c'est le but, extraire les champs à afficher dans notre page, l'idée la plus simple serait d'effectuer une requête complète puis de compter le nombre de résultats obtenus (taille du tableau de résultats) et de n'afficher que les résultats 0 à 19, 20 à 39, etc. pour un exemple d'affichage à 20 résultats par page. On se dit en plus qu'on n'effectue qu'une seule requête, ce qui est bien pratique. Cette méthode est à proscrire complètement!
Cela donnerait pour cet exemple, en considérant que l'on veut également afficher les noms des auteurs et des thèmes des livres :

Imaginons que le revendeur d'id=5 aie 10.000 livres en librairie, cette méthode est catastrophique pour plusieurs raisons :

• on demande à MySQL d'extraire 10.000 références complètes, avec jointure sur 3 tables et tri des 10.000 éléments sur une colonne (ASC ou DESC), ce qui peut prendre plusieurs dixièmes de secondes ou secondes et occuper une connexion MySQL pendant autant de temps.
• les résultats obtenus, soit un tableau de 5x10.000 éléments, seront d'une manière ou d'une autre stockés dans un tableau PHP à faire peur, et pouvant probablement dans d'autres cas dépasser la mémoire maximum attribuée à chaque script.

Une meilleure pratique sera de procéder en 2 temps : une première requête effectuant un COUNT simple en tenant compte des filtres courants sélectionnés par l'utilisateur, puis une seconde requête pour extraire uniquement les résultats à afficher sur la page, à l'aide des clauses OFFSET et LIMIT :

On n'effectue donc qu'un COUNT normalement très rapide si les tables sont correctement indéxées, puis l'extraction d'un tableau de taille 5x20 avec tri sur 20 valeurs, soit quelques millisecondes ou centièmes de secondes au total pour MySQL.

Comme vu dans cet exemple, il est important de limiter l'extraction des champs à ceux nécessaires à l'affichage dans la liste. Puisque généralement une liste comportera un sous-ensemble réduit d'attributs, éviter les "SELECT (*)" et préférer lister les champs uniquement indispensables ("SELECT livre.titre, livre.annee, livre.image").

Il est courant de rencontrer des cas plus complexes, où l'on a par exemple besoin de récupérer des données provenant de jointures "many-to-many". L'exemple le plus classique pour comprendre cette relation est l'attribution de tags : chaque tags peut être attribué à plusieurs livres et chaque livre peut posséder plusieurs tags.
Quelle que soit la situation, il faut à la fois simplifier la requête principale (minimiser le nombre de jointures) et éviter d'avoir à effectuer une requête supplémentaire par élément de la liste à afficher : si on affiche 100 éléments par page, au lieu d'effectuer 2 requêtes on en effectue 102..!
Une méthode répondant à ce besoin consiste à effectuer des sous-requêtes et utiliser des tableaux PHP. Pour afficher dans la liste de livres, pour chaque livre, la liste des tags associés, on peut procéder ainsi :

$liens = db::instance()->query('SELECT livre_id, tag_id FROM livre_tag
    WHERE livre_id IN ('.implode(',',$livreIDs).')');
$livres_tags = array();
$tagIDs = array();
foreach($liens as $lien){
    $livres_tags[$lien['livre_id']][] = $lien['tag_id'];
    $tagIDs[] = $lien['tag_id'];
}

$tags = db::instance()->query('SELECT id, nom FROM tag 
    WHERE id IN ('.implode(',',$tagIDs).')');
$tagnoms = array();
foreach($tags as $tag){
    $tagnoms[$tag['id']] = $tag['nom'];
}

foreach($livres_tags[$livreID] as $tagID){
    echo $tagnoms[$tagID].'<br/>';
}

Cette technique peut paraître un peu longue à écrire mais optimise au maximum les ressources serveur (seulement 4 requêtes minimisées au total pour afficher une liste de livres et leurs tags associés, quel que soit le nombre de livres affichés) et est assez systématique, elle peut donc être intégrée dans une classe générique de gestion de listes paginées.