Combiner des colonnes

Cette section porte sur un problème fréquent qui affecte les index concaténés.

Encore une fois, le premier exemple est sur les types date et heure. La requête MySQL suivante combine une colonne date et heure pour appliquer un filtre d'intervalle sur les deux.

SELECT ...
  FROM ...
 WHERE ADDTIME(colonne_date, colonne_heure)
     > DATE_ADD(now(), INTERVAL -1 DAY)

Elle sélectionne tous les enregistrements des dernières 24 heures. La requête ne peut pas utiliser un index concaténé (COLONNE_DATE, COLONNE_HEURE) correctement parce que la recherche n'est pas faite sur les colonnes indexées mais sur des données dérivées.

Vous pouvez éviter ce problème en utilisant un type de données qui a les deux composants date et heure (par exemple, le DATETIME de MySQL). Ainsi, vous pouvez utiliser cette colonne sans un appel de fonction :

SELECT ...
  FROM ...
 WHERE colonne_dateheure
     > DATE_ADD(now(), INTERVAL -1 DAY)

Malheureusement, il est souvent impossible de modifier la table lorsqu'on rencontre ce problème.

L'option suivante est un index fonctionnel si la base de données le supporte bien que cela ait tous les inconvénients discutés précédemment. De plus, avec MySQL, les index fonctionnels ne sont pas une option.

Il est toujours possible d'écrire la requête de telle façon que la base de données puisse utiliser un index concaténé sur COLONNE_DATE, COLONNE_HEURE avec un prédicat d'accès, au moins partiellement. Pour cela, nous ajoutons une condition supplémentaire sur COLONNE_DATE.

 WHERE ADDTIME(colonne_date, colonne_heure)
     > DATE_ADD(now(), INTERVAL -1 DAY)
   AND colonne_date
    >= DATE(DATE_ADD(now(), INTERVAL -1 DAY))

La nouvelle condition est complètement redondante mais elle est un filtre direct sur COLONNE_DATE, et donc elle peut être utilisée comme prédicat d'accès. Même si cette technique n'est pas parfaite, il s'agit d'une approximation suffisante.

Vous pouvez aussi utiliser cette technique lors du stockage de dates et heures dans des colonnes texte, mais vous devez utiliser les formats date et heure qui amènent un ordre chronologique lors d'un tri lexical, autrement dit comme suggéré par le standard ISO 8601 (YYYY-MM-DD HH:MM:SS). L'exemple suivant utilise la fonction TO_CHAR de la base de données Oracle dans ce but :

SELECT ...
  FROM ...
 WHERE chaine_date || chaine_heure
     > TO_CHAR(sysdate - 1, 'YYYY-MM-DD HH24:MI:SS')
   AND chaine_date
    >= TO_CHAR(sysdate - 1, 'YYYY-MM-DD')

Nous rencontrons de nouveau le problème de l'application d'une condition d'intervalle sur plusieurs colonnes dans la section titrée « Parcourir les résultats ». Nous allons aussi utiliser la même méthode d'approximation.

Quelques fois, nous avons le cas inverse et voulons cacher une condition de façon intentionnelle pour qu'elle ne soit plus utilisée comme prédicat d'accès. Nous avons déjà regardé ce problème lors de la discussion sur les effets des paramètres liés sur les conditions LIKE. Prenons l'exemple suivant :

SELECT nom, prenom, id_employe
  FROM employes
 WHERE id_supplementaire = ?
   AND nom LIKE ?

En supposant qu'il y ait un index sur ID_SUPPLEMENTAIRE et un autre sur NOM, lequel est préférable pour cette requête ?

Sans connaître la position du caractère joker dans le terme de recherche, il est impossible de donner une réponse justifiée. L'optimisateur n'a pas d'autres choix que de deviner. Si vous savez qu'il y a toujours un caractère joker en début, vous pouvez changer la condition LIKE de façon à ce que l'optimiseur ne considère plus la possibilité d'utiliser un index sur NOM.

SELECT nom, prenom, id_employe
  FROM employes
 WHERE id_supplementaire = ?
   AND nom || '' LIKE ?

Il suffit d'ajouter une chaîne vide à la colonne NOM. Néanmoins, c'est la solution de la dernière chance. Il ne faut le faire qu'en cas d'absolue nécessité.