Lenteur de requete lors de selection de colonnes trop importantes

**JJyoda** · 26/09/2007, 14h56

Bonjour à tous, je suis nouveau sur ce forum.
J'en viens à poster un message car je rencontre un problème depuis quelques années, que j'ai pu détourner jusqu'à maintenant. Mais la je ne veux plus le contourner. alors si quelqu'un a une idée, voici ce qui se passe.

J'ai une requête sous mysql 5.xx sous linux qui est la suivante qui rapatrie pas mal de champs, dont des champs longtext.

La seule présence des champs longtext dans la requête ralentie le temps d'exécution (ou plutôt de rappatriement) de cette dernière.

En exemple cette requête prend environ 1,50 secondes sur un vieux p4 1Ghz avec 512 Mo de ram :

select distinct produitsfiches.BarCode,produitsfiches.Designation, produitsfiches.Designation2,produitsfiches.Marque,produitsfiches.Modele ,produitsfiches.PrixVente ,produitsfiches_complements.PrixInfo ,produitsfiches_complements.PrixPromoInfo ,produitsfiches_complements.EnStock ,produitsfiches_complements.Barcode_Infos ,produitsfiches_complements.Barcode_Infos_Promo ,produitsfiches_complements.Barcode_Infos_Notes ,Flash360,FlashClip,FK_LIVA as IdBurst, VALEUR AS Burst, IMAGE AS VisuelBurst ,Famille,SsFamille,Rayon,Saison from rubrique inner join article_rubrique on RefRub=IdRub inner join produitsfiches on article_rubrique.Barcode=produitsfiches.Barcode inner join produitsfiches_complements on produitsfiches_complements.Barcode=produitsfiches.Barcode left join liste_valeurs_langue on produitsfiches_complements.IdBurst=liste_valeurs_langue.FK_LIVA where article_rubrique.Visible=1 and (FK_LANGUE=1 or FK_LANGUE is null) and VisibleWeb=1 and IdRub in (1,972,968,931,962,777,736,723,725,724,681,20,684,699,717,149,147,146,145,144,16,966,862,873,867,866,865,864,863,700,686,682,128,127,126,125,448,447,446,730,124,14,765,841,830,766,767,807,815,814,813,812,811,810,809,808,769,768,770,773,721,702,776,113,112,410,409,408,407,406,733,111,924,843,832,759,834,403,402,401,400,833,398,397,109,842,831,817,825,824,823,822,821,820,819,818,816,715,388,386,384,703,108,844,382,381,379,378,376,375,104,840,829,775,743,798,803,804,805,806,799,801,800,802,369,367,366,103,839,828,764,789,792,791,793,790,794,795,796,797,774,364,361,100,838,827,728,354,352,351,350,349,348,347,98,942,837,835,826,763,779,788,787,786,785,783,784,782,780,680,336,335,334,333,332,331,11,932,695,687,698,81,79,78,290,77,76,9,67,66,65,64,63,62,61,60,59,58,57,56,55,53,678) order by Marque

si après avoir vidé le cache de mysql, je fais la même requête en ne rapatriant par exemple que le champs Barcode, la requete se fait en 0.60 s environ, soit deux fois moins de temps, mais aussi 10 fois moins de données à rappatrier pour un même nombre de lignes.

select distinct produitsfiches.BarCode from rubrique inner join article_rubrique on RefRub=IdRub inner join produitsfiches on article_rubrique.Barcode=produitsfiches.Barcode inner join produitsfiches_complements on produitsfiches_complements.Barcode=produitsfiches.Barcode left join liste_valeurs_langue on produitsfiches_complements.IdBurst=liste_valeurs_langue.FK_LIVA where article_rubrique.Visible=1 and (FK_LANGUE=1 or FK_LANGUE is null) and VisibleWeb=1 and IdRub in (1,972,968,931,962,777,736,723,725,724,681,20,684,699,717,149,147,146,145,144,16,966,862,873,867,866,865,864,863,700,686,682,128,127,126,125,448,447,446,730,124,14,765,841,830,766,767,807,815,814,813,812,811,810,809,808,769,768,770,773,721,702,776,113,112,410,409,408,407,406,733,111,924,843,832,759,834,403,402,401,400,833,398,397,109,842,831,817,825,824,823,822,821,820,819,818,816,715,388,386,384,703,108,844,382,381,379,378,376,375,104,840,829,775,743,798,803,804,805,806,799,801,800,802,369,367,366,103,839,828,764,789,792,791,793,790,794,795,796,797,774,364,361,100,838,827,728,354,352,351,350,349,348,347,98,942,837,835,826,763,779,788,787,786,785,783,784,782,780,680,336,335,334,333,332,331,11,932,695,687,698,81,79,78,290,77,76,9,67,66,65,64,63,62,61,60,59,58,57,56,55,53,678) order by Marque

J'ai tenté pour l'experience le passage des champs lourds en donnée à null et la requete dans ce cas passe à 1 seconde.

Il est très évident que le select * n'est pas à utiliser, mais que faire quand on doit rapatrier certains champs obligatoirement.

J'ai tenté de jouer sur les paramètre mysql

read_buffer_size
net_buffer_length

sans succès.

Alors si quelq'un a une idée, de mon coté je sèche.

Pour info, le détournement revient à créer des fichiers textes contenant le fameux contenu à rapatrier, et la le problème ne se pose plus, mais la gestion des ecritures, ouvertures des fichiers peut engendrer des problèmes de nombre de fichiers et de lenteur si les disques du serveur ne sont pas formatée en ReiserFs. Et il faut aussi se pencher sur les temps d'ouverture des fichiers et des accès disques quand on remonte de nombreux enregistrements.

Bref, ce n'est pas une solution miracle.

merci de votre contribution si vous avez une solution.

**berceker united** · 26/09/2007, 15h01

Pourrais-tu mettre les requêtes dans les balises code et de les indenter? Parce que là c'est pas très lisible

**JJyoda** · 26/09/2007, 15h11

Désolé, voici le msg un peu mieux mis en page

Bonjour à tous, je suis nouveau sur ce forum.
J'en viens à poster un message car je rencontre un problème depuis quelques années, que j'ai pu détourner jusqu'à maintenant. Mais la je ne veux plus le contourner. alors si quelqu'un a une idée, voici ce qui se passe.

J'ai une requête sous mysql 5.xx sous linux qui est la suivante qui rapatrie pas mal de champs, dont des champs longtext.

La seule présence des champs longtext dans la requête ralentie le temps d'exécution (ou plutôt de rappatriement) de cette dernière.

En exemple cette requête prend environ 1,50 secondes sur un vieux p4 1Ghz avec 512 Mo de ram :

Code :

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
25
26
27
28
29
select distinct produitsfiches.BarCode,
produitsfiches.Designation,
produitsfiches.Designation2,
produitsfiches.Marque,
produitsfiches.Modele ,
produitsfiches.PrixVente ,
produitsfiches_complements.PrixInfo ,produitsfiches_complements.PrixPromoInfo ,produitsfiches_complements.EnStock ,produitsfiches_complements.Barcode_Infos ,produitsfiches_complements.Barcode_Infos_Promo ,produitsfiches_complements.Barcode_Infos_Notes ,Flash360
,FlashClip,FK_LIVA as IdBurst,
VALEUR AS Burst,
IMAGE AS VisuelBurst ,
Famille,
SsFamille,
Rayon,
Saison
 
from rubrique
 
inner join article_rubrique on RefRub=IdRub
inner join produitsfiches on article_rubrique.Barcode=produitsfiches.Barcode inner join produitsfiches_complements on produitsfiches_complements.Barcode=produitsfiches.Barcode
left join liste_valeurs_langue on produitsfiches_complements.IdBurst=liste_valeurs_langue.FK_LIVA
 
where 
 
article_rubrique.Visible=1
and (FK_LANGUE=1 or FK_LANGUE is null)
and VisibleWeb=1
and IdRub in (1,972,968,931,962,777,736,723,725,724,681,20,684,699,717,149,147,146,145,144,16,966,862,873,867,866,865,864,863,700,686,682,128,127,126,125,448,447,446,730,124,14,765,841,830,766,767,807,815,814,813,812,811,810,809,808,769,768,770,773,721,702,776,113,112,410,409,408,407,406,733,111,924,843,832,759,834,403,402,401,400,833,398,397,109,842,831,817,825,824,823,822,821,820,819,818,816,715,388,386,384,703,108,844,382,381,379,378,376,375,104,840,829,775,743,798,803,804,805,806,799,801,800,802,369,367,366,103,839,828,764,789,792,791,793,790,794,795,796,797,774,364,361,100,838,827,728,354,352,351,350,349,348,347,98,942,837,835,826,763,779,788,787,786,785,783,784,782,780,680,336,335,334,333,332,331,11,932,695,687,698,81,79,78,290,77,76,9,67,66,65,64,63,62,61,60,59,58,57,56,55,53,678) 
 
order by Marque

si après avoir vidé le cache de mysql, je fais la même requête en ne rapatriant par exemple que le champs Barcode, la requete se fait en 0.60 s environ, soit deux fois moins de temps, mais aussi 10 fois moins de données à rappatrier pour un même nombre de lignes.

Code :

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
select distinct produitsfiches.BarCode
 
from rubrique
 
inner join article_rubrique on RefRub=IdRub
inner join produitsfiches on article_rubrique.Barcode=produitsfiches.Barcode inner join produitsfiches_complements on produitsfiches_complements.Barcode=produitsfiches.Barcode
left join liste_valeurs_langue on produitsfiches_complements.IdBurst=liste_valeurs_langue.FK_LIVA
 
where 
 
article_rubrique.Visible=1
and (FK_LANGUE=1 or FK_LANGUE is null)
and VisibleWeb=1
and IdRub in (1,972,968,931,962,777,736,723,725,724,681,20,684,699,717,149,147,146,145,144,16,966,862,873,867,866,865,864,863,700,686,682,128,127,126,125,448,447,446,730,124,14,765,841,830,766,767,807,815,814,813,812,811,810,809,808,769,768,770,773,721,702,776,113,112,410,409,408,407,406,733,111,924,843,832,759,834,403,402,401,400,833,398,397,109,842,831,817,825,824,823,822,821,820,819,818,816,715,388,386,384,703,108,844,382,381,379,378,376,375,104,840,829,775,743,798,803,804,805,806,799,801,800,802,369,367,366,103,839,828,764,789,792,791,793,790,794,795,796,797,774,364,361,100,838,827,728,354,352,351,350,349,348,347,98,942,837,835,826,763,779,788,787,786,785,783,784,782,780,680,336,335,334,333,332,331,11,932,695,687,698,81,79,78,290,77,76,9,67,66,65,64,63,62,61,60,59,58,57,56,55,53,678) 
 
order by Marque

J'ai tenté pour l'experience le passage des champs lourds en donnée à null et la requete dans ce cas passe à 1 seconde.

Il est très évident que le select * n'est pas à utiliser, mais que faire quand on doit rapatrier certains champs obligatoirement.

J'ai tenté de jouer sur les paramètre mysql

read_buffer_size
net_buffer_length

sans succès.

Alors si quelq'un a une idée, de mon coté je sèche.

Pour info, le détournement revient à créer des fichiers textes contenant le fameux contenu à rapatrier, et la le problème ne se pose plus, mais la gestion des ecritures, ouvertures des fichiers peut engendrer des problèmes de nombre de fichiers et de lenteur si les disques du serveur ne sont pas formatée en ReiserFs. Et il faut aussi se pencher sur les temps d'ouverture des fichiers et des accès disques quand on remonte de nombreux enregistrements.

Bref, ce n'est pas une solution miracle.

merci de votre contribution si vous avez une solution.

**jeca** · 26/09/2007, 15h36

Bonjour,

J'ignore totalement l'incidence que ça peut avoir, mais as-tu essayé de trier les valeur du 'IN' par ordre croissant au lieu de les laisser en vrac ?

**berceker united** · 26/09/2007, 15h40

Problème c'est que ton IN(...) c'est l'équivalent de OR ... OR ... OR ... OR
Donc ça plombe les performances, surtout au vu nombre.
Est ce que c'est deux champs sont indexé ? RefRub et IdRub et Barcode ?

**JJyoda** · 26/09/2007, 15h41

Et bien cela n'a pas d'incidence réelle par rapport à mon problème qui vient comme je l'ai decrit à 90 % du "poids" de l'information à rappatrier...

La même requête met deux fois moins de temps si je ne rappartrie que une colonne.

**JJyoda** · 26/09/2007, 15h44

Oui les champs sont indéxés.

voici ce que donne un explain

Code :

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

1
2
3
4
5
6
7
8
9
10
 
+----+-------------+----------------------------+--------+--------------------------------------+---------+---------+-------------------------------------------------------+------+-----------------------------------------------------------+
| id | select_type | table                      | type   | possible_keys                        | key     | key_len | ref                                                   | rows | Extra                                                     |
+----+-------------+----------------------------+--------+--------------------------------------+---------+---------+-------------------------------------------------------+------+-----------------------------------------------------------+
|  1 | SIMPLE      | rubrique                   | range  | PRIMARY                              | PRIMARY | 4       | NULL                                                  |  203 | Using where; Using index; Using temporary; Using filesort |
|  1 | SIMPLE      | article_rubrique           | ref    | PRIMARY,BarCode,RefRub,Visible       | RefRub  | 4       | fastmag_hawaiisurf.rubrique.IdRub                     |   25 | Using where                                               |
|  1 | SIMPLE      | produitsfiches_complements | eq_ref | PRIMARY                              | PRIMARY | 22      | fastmag_hawaiisurf.article_rubrique.BarCode           |    1 |                                                           |
|  1 | SIMPLE      | liste_valeurs_langue       | ref    | FK_LIVA                              | FK_LIVA | 5       | fastmag_hawaiisurf.produitsfiches_complements.IdBurst |    1 | Using where                                               |
|  1 | SIMPLE      | produitsfiches             | eq_ref | PRIMARY,ParDesignation,ParVisibleWeb | PRIMARY | 22      | fastmag_hawaiisurf.article_rubrique.BarCode           |    1 | Using where                                               |
+----+-------------+----------------------------+--------+--------------------------------------+---------+---------+-------------------------------------------------------+------+-----------------------------------------------------------+

**JJyoda** · 26/09/2007, 15h46

En fait apres avoir essayé les combinaisons, les forcages d'index, les commandes indiquant que c'etait une grosse requete " SQL_BIG_RESULT", cela ne change rien de rien et le seul changement s'opere en jouant sur le nombre de champs à rappartier.. C tout, c'est simple à déduire, mais bien moins simple à résoudre....

**berceker united** · 26/09/2007, 16h02

Envoyé par JJyoda

En fait apres avoir essayé les combinaisons, les forcages d'index, les commandes indiquant que c'etait une grosse requete " SQL_BIG_RESULT", cela ne change rien de rien et le seul changement s'opere en jouant sur le nombre de champs à rappartier.. C tout, c'est simple à déduire, mais bien moins simple à résoudre....

Il y a une fonction mysql qui peut te permettre de lui dire déjà a l'avance que ça va être une grosse requête et c'est SQL_BIG_RESULT

**JJyoda** · 26/09/2007, 16h08

eh eh, c'est ce que je disais au dessus, ca n'y fait rien

**berceker united** · 26/09/2007, 16h14

Il faut faire des test et voir qui prend le plus de ressource.
- Enleve le ORDER BY et voir si tu y gagne.
- Enleve le IN(...) et voir si tu y gagne.
Essays de voir si tu ne peux pas jouer sur envoyer les données dans une table temporaire et faire la requête sur cette table.

**JJyoda** · 26/09/2007, 16h21

Ca aussi je l'ai pas décrit, mais je l'ai fait aussi.. Idem, toujours le même comportement.

**berceker united** · 26/09/2007, 16h30

Je viens de relire exactement ton post et apparement c'est purement lié au champs selectionné. Quels sont les types de chacun ?

**JJyoda** · 26/09/2007, 17h47

Les champs qui genent sont au nombre de 3 et sont en longtext. mais surtout il ya du contenu dedans dont j'ai besoin.

Ce sont des tableaux serialisés et encodés en base64 pour eviter les problèmes d'accents dans les contenus de tableaux serialises

du coup c'est un peu lourd

**JJyoda** · 26/09/2007, 17h48

Voici les champs

produitsfiches_complements.Barcode_Infos ,produitsfiches_complements.Barcode_Infos_Promo ,produitsfiches_complements.Barcode_Infos_Notes

**berceker united** · 27/09/2007, 02h28

As tu besoin d'avoir ces informations tous en même temps ? Parce que tu faire en sorte de l'avoir à la demande. Ainsi tu n'auras pas besoin de remonter toute les informations mais si le besoin se fait sentire tu l'appelles.

**JJyoda** · 27/09/2007, 15h37

Et bien malheureusement oui. Mais je vais finir par changer de fusil d'epaule et peut être réduire les infos passée au plus juste.

Mais en tant que tête chercheuse, et pointilliste, je ne peux m'empecher de me dire qu'il existe une solution de paramétrage de mysql à ce problème... Je ne sais pas trop ou le poster. Je vais aller voir du coté de mysql peut être...

**berceker united** · 27/09/2007, 16h13

Envoyé par JJyoda

Et bien malheureusement oui. Mais je vais finir par changer de fusil d'epaule et peut être réduire les infos passée au plus juste.

Mais en tant que tête chercheuse, et pointilliste, je ne peux m'empecher de me dire qu'il existe une solution de paramétrage de mysql à ce problème... Je ne sais pas trop ou le poster. Je vais aller voir du coté de mysql peut être...

Quel est le volume de donnée que tu remontes parce que je ne vois pas ce que tu peux parametrer si c'est parce qu'il y a un gros volume.