Probleme sur requête avec order by multiple

**dembroski** · 21/01/2010, 11h54

Bonjour, je galere sur une requete, je ne vois pas comment la faire :

Table :

photo_id(int 11) - couleur_id(int, 0 à 216) - pourcentage(float 11,10)

Ce que je cherche a faire : Sortir toutes les photos qui ont du noir superieur à 0.25 et du rouge supérieur à 0.25 en classant vous imaginez bien par celle qui ont le plus de rouge et de noir en meme temps, car mes requetes testé avec un order me classe par cel qui on le plus de noir et ensuite celle qui on le plus de rouge exemple :

Code :

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

1
2
3
4
5
6
7
8
Select a.photo_id 
from table a, table b 
where a.photo_id = b.photo_id 
and a.color_id = 1 
and b.color_id = 60 
and a.pourcentage > 0.25 
and b.pourcentage > 0.25 
order by a.pourcentage DESC, b.pourcentage DESC

Il me sor donc dans l'ordre cel qui on le plus de noir, puis sur les noir qui on le meme pourcentage, il tris la valeur du pourcentage rouge ce qui est logique mais pas ce que je cherche a faire...

Si quelqu'un à une idée.

MErci

**aurelienl** · 21/01/2010, 12h23

Salut,

Et si tu trie tous simplement suivant ce modele la:

Code :

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

ORDER BY (a.pourcentage + b.pourcentage) DESC

est ce que cela te convient?

**dembroski** · 21/01/2010, 12h26

L'idée n'est pas bete mais je supose que sa peu me sortir en premier une foto a 90% de rouge et 10% de noir alor que une photoà 50% de chaque est plus coherente dans le resultat

Le truc c'est qu'il n'y a pas de priorité sur l'ordre de couleur donc il faut que le order soit fait par paire(ou par plus que paire si je cherche pour 3 couleurs ou 10 ^^).

Merci quand meme

**Maljuna Kris** · 21/01/2010, 13h04

Saluton,
Essaye avec

Code sql :

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

ORDER BY (a.pourcentage * b.pourcentage) DESC

car 0.1 * 0.9 donne 0.09 alors que 0.5 * 0.5 donne 0.25

**ced** · 21/01/2010, 13h05

Bonjour,

Il faut que tu définisses ce que tu entends par "plus cohérent dans le résultat"...
Parce que c'est de ça que dépend la faisabilité ou pas du tri.

ced

**dembroski** · 21/01/2010, 13h28

Ok (merci pour la balise code je ferai avec la prochaine fois)

Je fais les tests aprs mangé :

Si tu a saisi mon but, c'est dafficher des photos (j'ai les valeur en pourcentages des 10 couleur les plus rependu dans cette photos sur une base de 216 couleurs)

Donc je veux faire une recherche en ajoutant une couleur : (a 25% de base au moins)
et je peux changer ce minimum, sa peut donc etre 10% ou plus, 30% ou plus etc.

De la je peux ajouter une deuxieme couleur et idem, mettre un pourcentage variable (ceci jusqu'a 10 couleurs)

Donc si je met 30%(au moins donc >=) de rouge, et 25%(au moins donc >=) de noir, tu te doute que c'est plus coherant de trouvé une image qui contient 50% de rouge et 45% de noir que de trouvé une image qui à 70% de rouge et 25% de noir car au depart dans ma recherche, l'ecart de mes critere est faible (50 -45 colle mieux à 30 -25 que 70 -25)

Tu saisi mieux?

**dembroski** · 21/01/2010, 14h21

pour trois couleurs, la requete ne finis pas avec le multiplié... faut savoir que j'ai 48 millions de relations dans ma table...

**dembroski** · 21/01/2010, 14h24

Envoyé par dembroski

pour trois couleurs, la requete met +200 sec... faut savoir que j'ai 48 millions de relations dans ma table...

Code :

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

1
2
3
4
5
6
7
8
9
10
11
12
13
 
SELECT a.photo_id, a.color_count, b.color_count,c.color_count
FROM photos_colors_new a, photos_colors_new b, photos_colors_new c
WHERE a.photo_id = b.photo_id AND a.photo_id=c.photo_id
AND a.color_id =1
AND a.color_count > 0.25
AND b.color_id =216
AND b.color_count > 0.25
AND c.color_id =55
AND c.color_count > 0.25
ORDER BY (
a.color_count * b.color_count * c.color_count
) DESC

d'ailleurs si cette requete est la mieu a votre sens pour ce que je veux faire, que dois je mettre en index pour cette requete?

**CinePhil** · 21/01/2010, 16h02

Avec la syntaxe normalisée depuis 1992 pour les jointures, ce sera déjà un peu mieux :

Code :

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

1
2
3
4
5
6
7
8
9
10
11
12
13
SELECT a.photo_id, a.color_count, b.color_count,c.color_count
FROM photos_colors_new a
INNER JOIN photos_colors_new b ON a.photo_id = b.photo_id
INNER JOIN photos_colors_new c ON a.photo_id=c.photo_id
WHERE  a.color_id =1
  AND a.color_count > 0.25
  AND b.color_id =216
  AND b.color_count > 0.25
  AND c.color_id =55
  AND c.color_count > 0.25
ORDER BY (
a.color_count * b.color_count * c.color_count
) DESC

Il faut indexer photo_id et color_id, voire color_count.

**dembroski** · 21/01/2010, 16h29

Ok, j'ai changer la requete, je suis d'accort que c'est plus propre donc j'ai modifier mais le resultat et le meme... requete bcp trop longue alors qu'on a en
primary:
"color_id et photo id"

et en index on a plusieur(pour d'autre requete) :
color_id
color_id, color_count
color_id, photo_id, color_count

**dembroski** · 21/01/2010, 16h30

Le explain sur cette requette donne :

Code :

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

1
2
3
4
5
 
id // select_type // table // type // possible_keys // key // key_len // ref // rows // Extra
1 // SIMPLE // c // range // PRIMARY,cid,cidcc,pcc // cidcc // 7 // NULL // 6396 // Using where; Using temporary; Using filesort
1 // SIMPLE // a // eq_ref // PRIMARY,cid,cidcc,pcc // PRIMARY // 11 // xxx.c.photo_id,const // 1 // Using where
1 // SIMPLE // b // eq_ref // PRIMARY,cid,cidcc,pcc // PRIMARY // 11 // xxx.c.photo_id,const // 1 // Using where

**CinePhil** · 21/01/2010, 17h14

1 // SIMPLE // c // range // PRIMARY,cid,cidcc,pcc // cidcc // 7 // NULL // 6396 // USING WHERE; USING TEMPORARY; USING filesort

Ceci indique qu'il retient 6396 lignes (sur 48 millions si j'ai bien compris dans un précédent message) et qu'il utilise une table temporaire pour ça.
Le serveur est-il suffisamment dimensionné ?

Quel temps d'exécution pour la même requête sans le ORDER BY ?

**dembroski** · 21/01/2010, 17h49

temps identique sans le order...

Bon je pense que le procédé n'est pas adequate... si quelqu'un pense a une autre methode :

on a des photos (avec l'id des 10 couleurs les plus répértorié et leur % en float 11,10)

et je voudrais sortir les id des photos par "couleur + pourcentage"

ex:

a une couleur : cherché toutes les photos de color_id 1 à color_count > 0.25

a 2 couleur : cherché toutes les photos de color_id = 1 à color_count > 0.25 et contenant aussi la couleur color_id 50 à color_count > 0.37

etc...

ma table avec photo_id, color_id, color_count et peut etre pas aproprié pour cette requete?

**dembroski** · 28/01/2010, 10h41

Personne n'aurais une meilleure structure de table avec c'est information pour reduire les temps de requete sur ce que je désire faire?

Merci a vous

**CinePhil** · 28/01/2010, 10h51

temps identique sans le order...

On peut avoir le nouvel EXPLAIN sans le ORDER ?

et en index on a plusieur(pour d'autre requete) :
color_id
color_id, color_count
color_id, photo_id, color_count

Je suppose qu'il y a plus de valeurs différentes sur color_count que sur color_id ?
Essaie d'inverser l'ordre des colonnes dans l'index double sur ces deux colonnes afin d'avoir un index multicolonne plus efficace ==> INDEX (color_count, color_id)

**dembroski** · 28/01/2010, 10h56

Ok je vais testé sa et je te dis le explain

**dembroski** · 28/01/2010, 11h01

LE truc a savoir, c'est que la requete au niveau du WHERE cherche une couleur AVEC un count superieur a une valeur. Cela pause t'il un probleme sur l'indéxation vus qu'on utilise pas d'égalité sur ce champs?

**dembroski** · 28/01/2010, 11h40

Bon c'est long sur 48million d'entrée donc en attendant, j'ai ptétr une meilleur structure pour la requete, qu'en pense tu ? (mais je sait pas trop l'ecrire si tu peux m'aider merci :

photo_id ColorIDcolorCount(on ajoute l'id couleur avec le pourcentage qui est en 0,......)
---------------------------------------------
154685 155,56985459526
154685 204,12598458584
97685 57,34153153153
97685 114,09845125421

Pour une couleur sa donneré sa : on a la couleur id et le pourcentage, on crée avan la requete les valeurs "idcolor+pourcentage" et "idcolor+1"

'SELECT photo_id FROM ma_table WHERE ColorIDcolorCount BETWEEN "idcolor+pourcentage" AND "idcolor+1"(exclu) ORDER BY ColorIDcolorCount DESC'
(avec superieur peut etre au lieu de between?)

Pour deux couleur la je bloque... sa doneré du style :

'SELECT photo_id FROM ma_table WHERE ColorIDcolorCount BETWEEN "idcolor+pourcentage" AND "idcolor+1"(exclu) AND ColorIDcolorCount BETWEEN "idcolor2+pourcentage2" AND "idcolor2+1"(exclu) ORDER BY (la je bloque et jesplique) '

Pour 2 couleurs :

id rouge on va dire c'est 210
id vert on va dire c'est 115

On cherche rouge a au moins 25% et vert a au moins 10%

on peut donc trouvé des photos a 60% de vert et 30% de rouge

Mais le order doit rendre celle qui on le plus de rouge superieur à 25% avec du vert superieur a 10%.

C'est pas evident. si tu a une proposition je prend ^^

**CinePhil** · 28/01/2010, 12h18

Envoyé par dembroski

LE truc a savoir, c'est que la requete au niveau du WHERE cherche une couleur AVEC un count superieur a une valeur. Cela pause t'il un probleme sur l'indéxation vus qu'on utilise pas d'égalité sur ce champs?

Je ne suis pas un spécialiste du fonctionnement interne des SGBD mais je pense que ça ne pose pas de problème.
Logiquement, l'index doit être trié de manière croissante donc quand il atteint la valeur souhaitée il doit prendre tout ce qui suit.

Essaie l'inversion de l'index et balance le EXPLAIN pour voir avant de passer à autre chose. Ta structure de table me semble cohérente et je n'imagine pas, pour le moment, de solution plus optimum.