Discussion :

[tom-k]

Bonjour

Je voudrais trouver la solution à un problème qui se trouve à la base de beaucoup de questions soulevées par les utilisateurs de SQL.
En gros on peut le réduire à la question suivante :

Comment formuler la requête SQL pour extraire un enregistrement qui est le suivant par rapport à un autre enregistrement point de vue d’un index ?

Pour être plus clair je vais me servir d’un exemple.

J’ai une table "matable" avec l’index "monindex".
"monindex" est composé de deux champs (champ_clef1,champ_clef2).
champ_clef1, champ_clef2 sont de type INTEGER (pour simplifier).
"matable" contient les enregistrements suivants (triés selon "monindex") :

champ_clef1 -- champ_clef2
----- 1 --------- 1 --------
----- 1 --------- 2 --------
----- 1 --------- 3 --------
----- 2 --------- 1 --------
----- 2 --------- 2 --------
----- 2 --------- 3 --------
----- 3 --------- 1 --------
----- 3 --------- 2 --------
----- 3 --------- 3 --------

Je veux récupérer l’enregistrement qui se trouve juste après (1,3) - donc (2,1)…

J’ai essayé la requête :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
SELECT * FROM matable FORCE INDEX monindex 
WHERE (champ_clef1,champ_clef2) > (1,3) 
LIMIT 1;

A première vue ça marche mais en ajoutant "EXPLAIN" devant j’ai constaté que :

1. aucun index était utilisé (SQL analyse tous les enregistrements)
2. il y un risque que le résultat dépend du hasard. Comme l’index n’est pas utilisé, le premier enregistrement trouvé (je ne demande que un) pourrait être (3,3)

Ensuite j’ai essayé une autre requête :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
SELECT * FROM matable 
WHERE champ_clef1=1 AND champ_clef2>3 OR champ_clef1>1
LIMIT 1;

Cette fois monindex était utilisé mais SQL malgré tout analysait plusieurs enregistrements au lieu de s’arrêter sur le premier trouvé. Après la lecture de la documentation j’ai compris que SQL a créé dans ce cas deux intervalles (à cause de "OR") et le résultat c’est la concaténation de ces intervalles ou plutôt le premier enregistrement de cette concaténation. Ici c’est pareil – ce n’est peut-être pas l’enregistrement que je cherche…
Pour être sûr il faudrait ajouter "ORDER BY champ_clef1, champ_clef2" à cette requête…
Bien, mais dans ce cas, SQL en interne va faire probablement un travail énorme (chaque intervalle peut comporter beaucoup d’enregistrements et tout ça il faut trier à la fin).
En bref, pour quelque chose qui paraît assez simple (en utilisant l’index il est théoriquement possible de trouver le bon enregistrement tout suite), on risque d’attendre la réponse longtemps.

Est-ce que quelqu’un pourrait m’aider ?

[Antoun]

Rapidement (je reviendrai sur le fond du problème s'il y a besoin), ta requête

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
SELECT * 
FROM matable 
WHERE champ_clef1=1 AND champ_clef2>3 OR champ_clef1>1
LIMIT 1;

peut s'écrire avec un row constructor :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
SELECT * 
FROM matable 
WHERE (champ_clef1, champ_clef2) > (1, 3)
LIMIT 1;

Tu testerais ce que ça donne pour ton index ?

au passage,

Question subsidaire : combien y a-t-il de lignes dans ta table ?

[tom-k]

Salut Antoun,

Je crois que tu as loupé ma première requête dans laquelle j'utilise "row constructor" (à une différence près: j'ai essayé en même temps de forcer l'utilisation d'index).
Comme tu peux lire dans le commentaire, SQL ne veut pas tenir compte de l'existence d'index.

Pour le nombre d'enregistrements - on peut supposer qu'il y en a 500000. Ma question est plutôt théorique. Je suis à la recherche de bonnes méthodes pour faire de différentes choses avant de me lancer vraiment dans la programmation...

Merci pour la mise en forme…

[Antoun]

Effectivement, j'avais complètement raté ta première requête
(à ma décharge, je postais depuis mon PDA).

On va déjà commencer par ajouter le ORDER BY, sinon le LIMIT est aléatoire et ça n'aide pas l'optimiseur à comprendre :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
SELECT * 
FROM matable 
WHERE (champ_clef1,champ_clef2) > (1,3) 
ORDER BY champ_clef1,champ_clef2
LIMIT 1;

[tom-k]

C'est vrai qu'il faut ajouter "ORDER BY" pour trouver ce qu'on cherche. Malheureusement (comme je l'ai écrit dans mon commentaire) SQL n'a pas l'intention d'utiliser l'index. Ceci veut dire qu'on va trier pratiquement (si on veut localiser la deuxième ligne) la table entière... Avec 500000 lignes ça fait du boulot...

Pour être plus précis (je viens de faire un test): avec "ORDER BY" l'index est quand même utilisé mais SQL analyse tous les enregistrements. Autrement dit, SQL parcourt la table entière en utilisant l'index (du coup le tri à la fin n'est pas nécessaire) - et tout ça pour me présenter un enregistrement qui se trouve tout au début...

[Antoun]

Ton index est bien une combinaison de tes deux clés ?