Optimisation requête avec type range [Résolu]

[Nudger]

Bonjour,

Je cherche à optimiser l'accès à une table dont l'une des colonne indexée est filtrée par une clause between.
Lorsque j'interroge la table en direct, tout va bien, l'index est bien exploité.
En revanche lorsque je joins une table l'index n'est plus utilisé complètement.

Voici les tables avec leurs indexes:

Code :

1
2
3
4
5
CREATE TABLE CLEES(id1 int(11), id2 int(11));
CREATE TABLE DONNEES(id1 int(11), id2 int(11), dt bigint);
 
CREATE INDEX ii ON CLEES(id1, id2);
CREATE INDEX ii ON DONNEES(id1, id2, dt);

C'est DONNEE.dt qui sera accédé via un between.

Petites précisions sur le contexte:
- Il y a plusieurs millions de lignes dans DONNEES
- CLEES est une table de travail temporaire et très petite (moins de 100 lignes en général)


Lorsque je fais une requête sur DONNEES avec un between:

Code :

1
2
DESCRIBE 
SELECT * FROM DATAS da WHERE da.id1=0 AND da.id2=0 AND da.dt BETWEEN 100000 AND 100005;

Là il me dit type = range.
A l'exécution, cette requête est instantanée.

Et lorsque je joins CLEES à DATAS:

Code :

1
2
3
DESCRIBE 
SELECT da.* FROM CLEES cl INNER JOIN DATAS da ON cl.id1=da.id1 AND cl.id2=da.id2
WHERE da.dt BETWEEN 100000 AND 100005;

Ici il me dit type = ref, il n'utilise plus l'index mais se limite à id1 et id2.
A l'exécution, cette requête met entre 3 et 4 secondes.

J'ai fait OPTIMIZE et ANALYZE.
J'ai tenté de remplacer between par < et >.
Mais rien n'y fait.

Si quelqu'un a une idée, je suis preneur.
Merci.

[CinePhil]

Quand tu fais un index multi-colonnes, seule la première colonne est indexée individuellement. Tu peux avoir ton index classé comme suit :
id1, id2, dt
1, 1, 1
1, 5, 12
1, 6, 58
2, 2, 6
2, 8, 24
3, 4, 5
3, 5, 12
...
On voit bien que id1 est bien classé dans l'ordre mais que id2 n'est classé que par rapport à id1 et que dt n'est classé que par rapport au couple {id1, id2}.

Dans la première requête, le WHERE opère sur les trois colonnes et l'index peut être utilisé.

Dans la seconde requête, la jointure s'opère sur les deux premières colonnes de l'index et celui-ci peut être utilisé sur la jointure mais la restriction (WHERE) n'opère que sur la troisième colonne qui n'est pas indexée individuellement donc l'index multi-colonnes ne peut pas être utilisé.

=> Il faut que tu crées un index individuel sur dt.

[Nudger]

Merci CinePhil.
C'est le genre de modifications dont je me méfie souvent.. peur qu'une autre requête se mette à mal travailler en voulant exploiter ce nouvel index à tort.

Mais en tout cas c'est déjà très efficace sur le cas de figure que j'ai présenté

Je vais juste devoir faire quelques tests de non régression pour les autres cas.