Discussion :

[thelover2fr]

Bonjour à tous,

je cherche à optimiser une requête qui prends plus de 87 secondes pour s'exécuter.
merci par avance pour vos idées et suggestion:

Code :

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

1
2
3
4
5
6
7
8
9
10
11
12
13
14
 
SELECT ID, Titre, Prix, Contact, Description,
 
(SELECT valeur from details where DT.ID = details.ID_Annonce and rubrique='Kilométrage') as Kilometrage,
(SELECT valeur from details where DT.ID = details.ID_Annonce and rubrique='Carburant') as Carburant,
(SELECT valeur from details where DT.ID = details.ID_Annonce and rubrique='Boîte de vitesse') as Boite_Vitesse,
(SELECT valeur from details where DT.ID = details.ID_Annonce and rubrique='Année-modèle') as Annee_modele,
Region, Ville, CodePostal
 
FROM annonces as DT 
 
WHERE
DT.Categorie like '%voiture%'
AND (DT.`Export` = '0') AND (DT.`Contact` <> '') AND (char_length(DT.`Contact`) = 10)

[MaitrePylos]

Tu pourrais commencé par modéliser ta base de données autrement et faire en sorte de ne pas avoir des données lourdes à traiter de façons redondante.

Je pense à Rubrique qui devrais être sortit de la table detail et créer une nouvelle table rubrique de 0-n/1-1.

une table toutes simple du genre :
Trubrique
id_rubrique
tLibelle

avec comme insertion
0 Kilometrage,
1 Carburant,
2 Boîte de vitesse
3 Année-modèle

ainsi tu pourrais déjà gagné pas mal de recherche

Code SQL :

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

1
2
3
4
5
 
 
SELECT ID, Titre, Prix, Contact, Description,
 
(SELECT valeur from details where DT.ID = details.ID_Annonce and rubrique= 0) as Kilometrage,

[rawsrc]

Bonjour,

ça sent une table EAV tout ça.

Tu peux aussi faire une jointure multiple :

Code sql :

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

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
SELECT 
    DT.ID,
    DT.Titre,
    DT.Prix,
    DT.Contact,
    DT.Description,
    DT.Region,
    DT.Ville,
    DT.CodePostal,
    k.valeur AS `Kilométrage`,
    c.valeur AS `Carburant`,
    b.valeur AS `Boîte_Vitesse`,
    a.valeur AS `Annee_modele`
FROM
    annonces DT
        INNER JOIN details k ON DT.ID = k.ID_Annonce AND k.rubrique = 'Kilométrage'
        INNER JOIN details c ON DT.ID = c.ID_Annonce AND c.rubrique = 'Carburant'
        INNER JOIN details b ON DT.ID = b.ID_Annonce AND b.rubrique = 'Boîte de vitesse'
        INNER JOIN details a ON DT.ID = a.ID_Annonce AND a.rubrique = 'Année-modèle'
WHERE
    DT.Categorie LIKE '%voiture%'
    AND (DT.`Export` = '0') 
    AND (DT.`Contact` <> '') 
    AND (char_length(DT.`Contact`) = 10)

N'oublie pas poser un index sur rubrique.
Et si tu n'es pas sûr de l'existence d'une rubrique remplace INNER par LEFT

[thelover2fr]

milles merci pour vos réponses.
@MaitrePylos: mon soucis c'est que c'est une base d'un client et j'ai un accès en lecture dessus, je ne peux rien changé.
@rawsrc: merci pour l'astuce, cependant ça prends encore plus de temps et ça rembobine sans fin.

[rawsrc]

Oui j'ai laissé trainer une virgule en trop ligne 15. Je corrige.

[thelover2fr]

Oui je l'ai corrigé, mais comme indiqué dessus j'ai l'impression que ça boucle sans fin

[rawsrc]

@rawsrc: merci pour l'astuce, cependant ça prends encore plus de temps et ça rembobine sans fin.

Est ce que tu as un index sur rubrique ?
ça rembobine... ?

[rawsrc]

Poste voir tes index, stp.

[thelover2fr]

oui je veux dire mysql tente d'exécuter la requete ma sans résultat. ça charge dans le vide.
autrement je vous réexplique ma problématique vous saurez peu etre me proposer une autre requete:
j'ai une première table qui contient les annonces sous la forme:

ID | categorie | Titre | Prix | Contact | Description | Region | Ville | CodePostal

et une deuxième qui contient les détailles de l'annonce sous la forme

ID_Annonce | Rubrique | valeur
1 | kilometrage | 150000km
1 | carburant | diesel
2 | kilometrage | 45000


le but final c de transformer les lignes de la deuxième table en colonnes sur la première pour avoir sur chaque ligne l'annonce et ses détails représentées dans 2ème table

et donc dans la deuxième table les info sont représentés.

j'espère que j'étais assez clair.

[thelover2fr]

Poste voir tes index, stp.

Oui j'ai mis un index sur rubrique comme vous me l'avez suggérer

[rawsrc]

le but final c de transformer les lignes de la deuxième table en colonnes sur la première pour avoir sur chaque ligne l'annonce et ses détails représentées dans 2ème table

et donc dans la deuxième table les info sont représentés.

Bon c'est bien ce que je pensais : table EAV avec rendu PIVOT.
Je peux te le dire cash pour m'y être sacrément frotté, MySQL est complètement inapte à gérer simplement cette problématique. Tout ce que tu feras c'est des usines à gaz.

Un peu de lecture ici
Après, il te reste la procédure stockée avec table temporaire : bon courage.

Et au final, tu abandonneras MySQL pour Postgres qui lui gère ça nativement avec CROSSTAB().

Avant de t'ouvrir les veines, est ce que tu as un index sur le champ rubrique ? Je te pose la question parce faire appel à un champ dans un JOIN sans que ce dernier soit indexé est très mauvais pour les perfs.

[rawsrc]

Ok pour l'index, et malgré la présence d'un index, ça rame...
Grosses tables ?
MySql version ?

[thelover2fr]

Oui rawsrc j'ai ajouté un index sur rubrique. mais j'ai dit ça n'a rien changé.
si je fait une réplication sur un serveur postgreSQL et que je travaille sur celui là, est ce que tu pense que ce sera une bonne idée.
si c'est le cas peux tu adapter la requete à la syntaxe postgre car je ne l'ai pas utilisé au paravant et donc jene connais pas le CROSSTAB()

[thelover2fr]

Ok pour l'index, et malgré la présence d'un index, ça rame...
Grosses tables ?
MySql version ?

400.000 enregistrement dans la première et plus d'un million sur la deuxième.
Mysql 5.1

[rawsrc]

Ok, tu as donc accès aux index. Est ce que tu peux ajouter un index sur details (ID_Annonce, rubrique) et réessayer.

[thelover2fr]

Ok, tu as donc accès aux index. Est ce que tu peux ajouter un index sur details (ID_Annonce, rubrique) et réessayer.

rawsrc tu es magique, milles milles merci.
ça m'affiche mes résultats en un clin d'oeuil maintenant.
merci beaucoup.
j'aai une autre faveur à te demander. j'ai ajouté un

Code :

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

1
2
3
4
 
INTO OUTFILE 'C:\export.csv'
  FIELDS TERMINATED BY ';' OPTIONALLY ENCLOSED BY '"'
  LINES TERMINATED BY '\n'

à la requete pour exporter mes résultats dans un fichier csv, mais il me les sort avec des caractères accentués mal formatés (é, À, è, etc...)
y'a t il un moyen pour forcer l'encodage en utf8 lors de l'export des résultat?

[rawsrc]

rawsrc tu es magique

Est ce tu fais un

Code :

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

SET NAMES utf8;

avant d'exporter ?
Pour éviter ces problèmes, vérifie aussi la cohérence des encodages sur toute la chaîne : BDD PHP HTML...

[thelover2fr]

je viens de le faire mais ça n'a pas résolu le problème

[rawsrc]

Est ce que tu as essayé quelque chose comme ça en forçant l'encodage :

Code sql :

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

SELECT x COLLATE utf_general_ci ... INTO OUTFILE...

[thelover2fr]

Est ce que tu as essayé quelque chose comme ça en forçant l'encodage :

Code sql :

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

SELECT x COLLATE utf_general_ci ... INTO OUTFILE...

je t'avais bien dit que t'étais magique
milles merci cher ami