Optimisation d'une requête [Résolu]

[Fred_34]

Bonjour à tous,

J'ai une table en InnoDB qui a 2 millions d'enregistrements. (C'est uniquement parce que j'ai fait tourner un programme de test toute la nuit)

Etant donné que cette table est énorme, je me suis aperçu qu'une de mes requêtes est lente ( +60s):

Code :

1
2
 
SELECT MAX(IndexedColumn) FROM ( myBigTable) WHERE aColumn=1

Pour info, cette requête est bien instantanée:

Code :

1
2
 
SELECT MAX(IndexedColumn) FROM  ( myBigTable)

Dans la colonne "aColumn", il n'y a que 2 valeurs différentes (1 et 2)
Il y a 2 millions d’enregistrements ayant "aColumn" avec la valeur 1 et 4000 ayant la valeur 2.

Vu le temps, j'ai l'impression que mysql utilise d'abord la colonne where, puis sur tous les résultats prends le max.
Alors qu'il serait plus performant de retourner le premier max ou la condition est vrai.

Existe-il une autre façon d'écrire cette requête ?

Merci.

[punkoff]

Alors qu'il serait plus performant de retourner le premier max ou la condition est vrai.

Et comment il devine que la condition est vrai ?

Donc oui il est forcé de faire un scannage de table puis de trouver le max de ceci.

Et je pense qu'un index du type : (aColumn, IndexedColumn) ne changera pas grand chose à cause de la diversité des données.

[Maljuna Kris]

Saluton,
Vu le contexte, essaye comme cela

Code :

SELECT MAX(IndexedColumn) FROM ( myBigTable) GROUP BY aColumn

Cela retournera certes deux lignes, mais peut-être plus vite.

[Fred_34]

Envoyé par Maljuna Kris

Saluton,
Vu le contexte, essaye comme cela

Code :

SELECT MAX(IndexedColumn) FROM ( myBigTable) GROUP BY aColumn

Cela retournera certes deux lignes, mais peut-être plus vite.

Je viens de tester avec le "GROUP BY" et c'est le même temps.

[Fred_34]

Envoyé par punkoff

Et comment il devine que la condition est vrai ?

Donc oui il est forcé de faire un scannage de table puis de trouver le max de ceci.

Et je pense qu'un index du type : (aColumn, IndexedColumn) ne changera pas grand chose à cause de la diversité des données.

Je me suis peut être mal exprimé.

Je pense que si il existe une requête qui permet de faire faire à Mysql quelque chose comme ça ce serait plus rapide (La requête n'est pas bonne, c'est juste pour donner une idée):

Code :

1
2
3
4
5
6
7
8
9
10
 
lastmax=MAX;
while(1) {
 
  SELECT aColumn, MAX(IndexedColumn) AS lastMax FROM ( myBigTable) WHERE IndexedColumn<lastMax;
 
  IF(aColumn==1) {
    RETURN lastmax;
  }
}

Dans mon cas, il aurait au pire 4000 MAX à chercher et 4000 conditions à tester. (Ce qui doit probablement prendre moins de 60s)

[punkoff]

Envoyé par Fred_34

Je me suis peut être mal exprimé.

Je pense que si il existe une requête qui permet de faire faire à Mysql quelque chose comme ça ce serait plus rapide (La requête n'est pas bonne, c'est juste pour donner une idée):

Code :

Dans mon cas, il aurait au pire 4000 MAX à chercher et 4000 conditions à tester. (Ce qui doit probablement prendre moins de 60s)

Dans votre 1er poste vous dites que 4000 lignes ont la valeur 2 et .....1996000 ont la valeur 1.
Ici vous cherchez le max de la valeur qui a 1. Donc non votre boucle while est encore plus archaïque que ce qu le sgbd fait et il n'y a pas moyen de l'optimiser. testez avec un index comme je vous l'ai indiqué mais il y a de grande chance qu'il ne l'utilise pas.


En fait vous cherchez la 1ere ligne qui dispose de la condition aColumn = 1 ?

A quoi sert le MAX dans votre 2eme exemple ?

[CinePhil]

Teste si cette requête est plus rapide :

Code :

1
2
3
SELECT IndexedColumn 
FROM myBigTable 
WHERE aColumn = 1

Si c'est le cas, essaie ceci :

Code :

1
2
3
4
5
6
7
CREATE TEMPORARY TABLE temp
SELECT IndexedColumn 
FROM myBigTable 
WHERE aColumn = 1;
 
SELECT MAX(IndexedColumn)
FROM temp;

Sinon, vu ce que tu dis ici :

C'est uniquement parce que j'ai fait tourner un programme de test toute la nuit

Est-ce que par hasard tes données sont ordonnées avec d'abord les valeurs 1 puis les valeurs 2 et que tu cherches le dernier id auto-incrémenté correspondant à la valeur 1 ?

Si cette supposition est juste et si tu n'as pas supprimé des données, ceci devrait être plus rapide :

Code :

1
2
3
4
5
6
7
CREATE TEMPORARY TABLE temp
SELECT IndexedColumn
FROM myBigTable
WHERE aColumn = 2;
 
SELECT MIN(IndexedColumn) - 1 AS last_id_with_value_1
FROM temp

[punkoff]

Et ceci ?

Code :

1
2
3
4
5
6
 
SELECT IndexedColumn 
FROM bigtable
WHERE aColumn = 1
ORDER BY IndexedColumn  DESC
LIMIT 1

Si IndexedColumn est vraiment indexé, logiquement il devrait faire un scan d'index en partant du max et checker la condition après.

En viendra le limit.

[Fred_34]

Envoyé par CinePhil

Teste si cette requête est plus rapide :

Code :

Cette requête prends 1,7s

Envoyé par CinePhil

Si c'est le cas, essaie ceci :

Code :

Aye : Le CREATE à pris 75 secondes et le SELECT 2,5s

J'ai compris pourquoi la première requete avait été rapide :
- Je fais les tests des différentes requêtes avec workbench. Il avait ajouté limit 0,1000 au select, d'ou les 1.7s. Avec un limit 0,3000000 on retombe dans les +60s

Envoyé par CinePhil

Est-ce que par hasard tes données sont ordonnées avec d'abord les valeurs 1 puis les valeurs 2 et que tu cherches le dernier id auto-incrémenté correspondant à la valeur 1 ?

Non, ce n'est pas le cas.

[Fred_34]

Envoyé par punkoff

Et ceci ?

Code :

Si IndexedColumn est vraiment indexé, logiquement il devrait faire un scan d'index en partant du max et checker la condition après.

En viendra le limit.

Cette requête donne le bon résultat et en 2s !

Maintenant, je fais face à un dilemme. Dois-je généraliser cette requête partout ou je fais des SELECT MAX() avec une condition WHERE qui ne porte pas sur l'index recherché...

En tout cas, merci à tous les deux pour votre aide.

[CinePhil]

Nous ne t'avons pas conseillé de mettre un index sur aColumn car elle ne peut prendre que deux valeurs et l'index ne serait probablement pas utilisé. Ceci dit, tu peux essayer.

Si par contre tu as une recherche sur une autre colonne comptant un grand nombre de valeurs, l'index sera utilisé et ce genre de requête devrait être beaucoup plus rapide.

À tester au cas par cas.

Il me semble quand même un peu long qu'une recherche d'un MAX sur 2 millions de lignes mette autant de temps, même sans index sur la colonne cherchée !

[Fred_34]

Envoyé par CinePhil

Nous ne t'avons pas conseillé de mettre un index sur aColumn car elle ne peut prendre que deux valeurs et l'index ne serait probablement pas utilisé. Ceci dit, tu peux essayer.

Si par contre tu as une recherche sur une autre colonne comptant un grand nombre de valeurs, l'index sera utilisé et ce genre de requête devrait être beaucoup plus rapide.

À tester au cas par cas.

Il me semble quand même un peu long qu'une recherche d'un MAX sur 2 millions de lignes mette autant de temps, même sans index sur la colonne cherchée !

J'avais commencé par mettre un index sur aColumn, mais ça n'avait pas changé grand chose. Ce qui a priori est normal vu qu'un index sur une colonne qui a très peu de valeurs possibles ne sert à rien.

Pour les performances : Je fais ces tests sur un PC portable et je prends bien soin d’arrêter mysql entre chaque tests afin qu'il n'y ai rien en cache.

En fait, au lieu de "généraliser cette requête a tous les SELECT MAX() avec une condition WHERE qui ne porte pas sur l'index recherché"
Ce serait plutôt "généraliser cette requête partout ou je fais des SELECT MAX(anIndexedColumn) avec une condition WHERE qui ne porte pas sur un index"

[CinePhil]

Envoyé par Fred_34

avec une condition WHERE qui ne porte pas sur un index"

Si cette colonne non indexée a un grand nombre de valeurs différentes, il faut envisager de l'indexer.
Attention toutefois à ce que la taille des index ne dépasse pas la taille de la table ; ça deviendrait contre-productif.

[Fred_34]

Voici les explains des différentes requêtes que j'ai testé. (Désolé pour le formatage)

Code :

1
2
EXPLAIN SELECT max(indexedColumn)
FROM myBigTable

id, select_type, table, type, possible_keys, key, key_len, ref, rows, Extra
1, SIMPLE, NULL, NULL, NULL, NULL, NULL, NULL,NULL,"Select tables optimized away"

Code :

1
2
3
4
5
EXPLAIN SELECT indexedColumn
FROM myBigTable
WHERE nonIndexedColumn = 1
ORDER BY indexedColumn DESC
LIMIT 1

id, select_type, table, type, possible_keys, key, key_len, ref, rows, Extra
1, SIMPLE, myBigTable,index,NULL,indexedColumn_IDX,4,NULL,1,"Using where"

Code :

1
2
3
EXPLAIN SELECT max(indexedColumn)
FROM myBigTable
WHERE nonIndexedColumn = 1

id, select_type, table, type, possible_keys, key, key_len, ref, rows, Extra
1,SIMPLE,myBigTable,ALL,NULL,NULL,NULL,NULL,212130,"Using where"

J'ai l'impression que Mysql n'utilise pas le fait que indexedColumn soit un index.

D'ailleurs si on fait un explain sur cette requête :

Code :

1
2
3
EXPLAIN SELECT max(anotherNonIndexedColumn)
FROM myBigTable
WHERE nonIndexedColumn = 1

On obtient le même résultat :
id, select_type, table, type, possible_keys, key, key_len, ref, rows, Extra
1,SIMPLE,myBigTable,ALL,NULL,NULL,NULL,NULL,212130,"Using where"

Entre temps, j'ai supprimé cette grosse table. Je fais tourner mon programme de test pour récréer une table de plusieurs millions d'enregistrements et pouvoir vérifier tout ça.

Edit :
J'obtiens bien les mêmes temps de réponses pour SELECT MAX(indexedColumn) WHERE... et SELECT MAX(nonIndexedColumn) WHERE...