Discussion :

[Arkyano]

Bonjour
en php, j'essaie de récupérer les valeurs d'une requête SQL sur plusieurs tables MYSQL
Mais celle-ci prend vraiment beaucoup de temps
en résultat, j'attends environ 400000 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
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
 
SELECT
  date_format((LEFT(`requete`.DATE, 10)), '%Y'),
  date_format((LEFT(`requete`.DATE, 10)), '%m'),
  `requete`.`CODE`,
  `requete`.`PAYS`,
  `requete`.`REF`,
  `requete`.`STATUT`,
  `requete`.`SECTEUR`,
  `requete`.`DEVISE`,
  sum(`requete`.`QTE`),
  `requetef`.`F`,
  `requeter`.`R`,
  `requete2`.`AC`,
  sum(`requete`.`PB`),
  sum(`requete`.`PN`),
  `requete2`.`AC` * (sum(`requete`.`QTE`))
 
FROM
  `requete`
  LEFT OUTER JOIN `requete2` ON (`requete2`.`REF` = `requete`.`REF`)
 
LEFT OUTER JOIN `requeter` ON (
  `requeter`.`REF` = `requete`.`REF`
  AND  `requeter`.`DATE` =
    (
    SELECT MAX(subqr.`DATE`)
    from `requeter` subqr
    where 1=1
    and subqr.`REF` = `requete`.`REF`
    and subqr.`DATE`<= `requete`.DATE
    )
  )
 
LEFT OUTER JOIN `requetef` ON (
  `requetef`.`REF` = `requete`.`REF`
  AND `requetef`.`DATE` =
  (
    SELECT MAX(subq.`DATE`)
    from `requetef` subq
    where 1=1
    and subq.`REF` = `requete`.`REF`
    and subq.`DATE`<= `requete`.DATE
    )
  )
 
WHERE
  date_format((LEFT(`requete`.DATE, 10)), '%Y') = '2016'
 
GROUP BY
  date_format((LEFT(`requete`.DATE, 10)), '%Y'),
  date_format((LEFT(`requete`.DATE, 10)), '%m'),
  `requete`.`CODE`,
  `requete`.`PAYS`,
  `requete`.`REF`,
  `requete`.`STATUT`,
  `requete`.`SECTEUR`,
  `requete`.`DEVISE`

puis-je optimiser ma requête ? où le problème ne vient pas de là ?
Merci

[Dendrite]

Bonjour.

Tu peux montrer le SQL de construction de la structure de tes tables s'il te plaît ?

Je commence par 2 détails :
1) .date et .ref sont des mots réservés SQL, donc ne surtout pas oublier les échappements
2) Pourquoi ne pas utiliser les fonctions year() et month() pour extraire année et mois d'une date ?

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
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
 SELECT
  YEAR(`requete`.`DATE`) as annee,
  MONTH(`requete`.`DATE`) as mois,
  `requete`.`CODE`,
  `requete`.`PAYS`,
  `requete`.`REF`,
  `requete`.`STATUT`,
  `requete`.`SECTEUR`,
  `requete`.`DEVISE`,
  sum(`requete`.`QTE`) as qutes,
  `requetef`.`F`,
  `requeter`.`R`,
  `requete2`.`PRIX`,
  sum(`requete`.`PB`) as pbs,
  sum(`requete`.`PN`) as pns,
  `requete2`.`AC` * (sum(`requete`.`QTE`))
 
FROM
  `requete`
  LEFT OUTER JOIN `requete2` ON (`requete2`.`ARTICLE` = `requete`.`ARTICLE`)
 
LEFT OUTER JOIN `requeter` ON (
  `requeter`.`ARTICLE` = `requete`.`ARTICLE`
  AND  `requeter`.`DATE` =
    (
    SELECT MAX(subqr.`DATE`)
    from `requeter` subqr
    where  subqr.`ARTICLE` = `requete`.`ARTICLE`
    and subqr.`DATE`<= `requete`.`DATE`
    )
  )
 
LEFT OUTER JOIN `requetef` ON (
  `requetef`.`ARTICLE` = `requete`.`ARTICLE`
  AND `requetef`.`DATE` =
  (
    SELECT MAX(subq.`DATE`)
    from `requetef` subq
    where  subq.`ARTICLE` = `requete`.`ARTICLE`
    and subq.`DATE`<= `requete`.`DATE`
    )
  )
 
WHERE
  YEAR(`requete`.`DATE`) = '2016'
 
GROUP BY
   YEAR(`requete`.`DATE`),
   MONTH(`requete`.`DATE`),
  `requete`.`CODE`,
  `requete`.`PAYS`,
  `requete`.`REF`,
  `requete`.`STATUT`,
  `requete`.`SECTEUR`,
  `requete`.`DEVISE`

[Arkyano]

Bonjour
je vous fait ça dès que possible
pour les dates, elles sont de base, aux formats 00/00/0000 00h:00m:00s, ça ira ?
ok pour les échappements

[Dendrite]

Une des techniques vers lesquelles j'irais, c'est de commencer par tes sous requêtes et de faire des tables temporaires. Ensuite un jointure externe vers une table temporaire te fera gagner un temps fou, plutôt que des sous requêtes ou des sous-sous requêtes... Autre chose, bien vérifier que tes clés primaires, clés uniques, clés étrangères, indexes sont tous bien posés. C'est essentiel pour la performance.
Est-ce que YEAR fonctionne sur un datetime ? Teste sur ta base :

Code SQL :

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

select YEAR('2016-12-24 12:00:02') as annee;

[Dendrite]

Sans garantie aucune, que donne ceci ?

Code SQL :

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

1
2
3
4
5
6
7
8
9
10
11
select distinct requeter.`ARTICLE` as article_r,requeter.R, requeter.`DATE` as date_r 
from requete
left outer join `requeter` ON 
`requeter`.`ARTICLE` = `requete`.`ARTICLE`
AND  `requeter`.`DATE` =(
	SELECT MAX(subqr.`DATE`)
	from `requeter` subqr
	where  subqr.`ARTICLE` = `requete`.`ARTICLE`
	and subqr.`DATE`<= `requete`.`DATE`
)
WHERE YEAR(`requete`.`DATE`) = 2016;

[Arkyano]

merci de vos réponses

Une des techniques vers lesquelles j'irais, c'est de commencer par tes sous requêtes et de faire des tables temporaires.

en fait, j'ai fait pas mal de tables dans MYSQL contenant mes données bruts, et là c'est la requête qui va regrouper le tout, c'est ça que vous vouliez dire ?

Ensuite un jointure externe vers une table temporaire te fera gagner un temps fou, plutôt que des sous requêtes ou des sous-sous requêtes...

ensuite je sélectionne le max valeur selon la date, par contre, mes tables ne sont par contre pas temporaire, mais je trunc/load into les données selon une fréquence

Autre chose, bien vérifier que tes clés primaires, clés uniques, clés étrangères, indexes sont tous bien posés. C'est essentiel pour la performance.

j'en ai mis aucune (clés primaires, clés uniques, clés étrangères), juste des index B-tree sur REF et CODE, car se sont les valeurs en commun entre toutes mes tables

Est-ce que YEAR fonctionne sur un datetime ? Teste sur ta base :

Code :

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

1
2
3
4
5
6
7
8
9
10
11
select distinct requeter.`ARTICLE` as article_r,requeter.R, requeter.`DATE` as date_r 
from requete
left outer join `requeter` ON 
`requeter`.`ARTICLE` = `requete`.`ARTICLE`
AND  `requeter`.`DATE` =(
	SELECT MAX(subqr.`DATE`)
	from `requeter` subqr
	where  subqr.`ARTICLE` = `requete`.`ARTICLE`
	and subqr.`DATE`<= `requete`.`DATE`
)
WHERE YEAR(`requete`.`DATE`) = 2016;

je test ça dès demain

encore merci

[Dendrite]

Ah, tu ne sais pas ce qu'est une table temporaire...
Comment résumer ça ? C'est une table que tu construis JUSTE pour le temps que dure ta connexion.
Ainsi, comme si c'était une table en dur, elle stocke des données sur un seul niveau. Elle est donc construite dynamiquement avec une requête. Vérifie d'abord que chaque requête fonctionne bien sûr !
Ensuite, quand tu clos ta session, elle meurt définitivement et ne laisse aucune trace.

Ici, 2 tables temporaires sont créées au préalable, puis on prend ensuite la table requete qui fait ensuite le LEFT JOIN sur ces 2 tables + requete2

Si la requête montrée précédemment fonctionne... l'idée serait la suivante.

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
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
CREATE TEMPORARY TABLE t_requete_r
select distinct requeter.`ARTICLE` as article_r,requeter.R, requeter.`DATE` as date_r 
from requete
left outer join `requeter` ON 
`requeter`.`ARTICLE` = `requete`.`ARTICLE`
AND  `requeter`.`DATE` =(
	SELECT MAX(subqr.`DATE`)
	from `requeter` subqr
	where  subqr.`ARTICLE` = `requete`.`ARTICLE`
	and subqr.`DATE`<= `requete`.`DATE`
)
WHERE YEAR(`requete`.`DATE`) = 2016;
 
CREATE TEMPORARY TABLE t_requete_f	
select distinct requetef.`ARTICLE` as article_f,requetef.F, requetef.`DATE` as date_f
LEFT OUTER JOIN `requetef` ON
`requetef`.`ARTICLE` = `requete`.`ARTICLE`
AND  `requetef`.`DATE` =(
	SELECT MAX(subq.`DATE`)
	from `requetef` subq
	where  subq.`ARTICLE` = `requete`.`ARTICLE`
	and subq.`DATE`<= `requete`.`DATE`
)
WHERE YEAR(`requete`.`DATE`) = 2016;
SELECT
  YEAR(`requete`.`DATE`) as annee,
  MONTH(`requete`.`DATE`) as mois,
  `requete`.`CODE`,
  `requete`.`PAYS`,
  `requete`.`REF`,
  `requete`.`STATUT`,
  `requete`.`SECTEUR`,
  `requete`.`DEVISE`,
  sum(`requete`.`QTE`) as qutes,
  `t_requete_f`.`F`,
  `t_requete_r`.`R`,
  `requete2`.`PRIX`,
  sum(`requete`.`PB`) as pbs,
  sum(`requete`.`PN`) as pns,
  `requete2`.`AC` * (sum(`requete`.`QTE`))
FROM `requete`
LEFT OUTER JOIN `requete2` ON `requete2`.`ARTICLE` = `requete`.`ARTICLE`
LEFT OUTER JOIN `t_requete_r` ON requete.article=t_requete_r.article
LEFT OUTER JOIN `t_requete_f` ON requete.article=t_requete_f.article
WHERE YEAR(`requete`.`DATE`) = 2016
GROUP BY
   YEAR(`requete`.`DATE`),
   MONTH(`requete`.`DATE`),
  `requete`.`CODE`,
  `requete`.`PAYS`,
  `requete`.`REF`,
  `requete`.`STATUT`,
  `requete`.`SECTEUR`,
  `requete`.`DEVISE`
 order by mois, `CODE`, `PAYS`, `REF`, `STATUT`, `SECTEUR`, `DEVISE`;

Exemple plus abordable :

Requete 1 : va me chercher tous les enfants des personnels entre 0 et 15 ans et stocke les sur une table temporaire t_enfant.
Ensuite, vraie requête, va me chercher tous les cadeaux distribués à ces enfants à l'arbre de Noel, en fonction de leur sexe et de leur age... Liaison entre table cadeau et t_enfant.
Les performances seront infiniment meilleures que si tu fais une sous-requête sur 2 niveaux à partir de la table employe puis enfant puis cadeau...

[Arkyano]

mais ça m'a l'air très bien comme idée
je suis pressé de tester tout ça

c'est vrai que je travail sur des centaines de milliers de lignes ça ne peut qu'aider. Je verrai aussi pour les clés primaires si toujours utiles sauf que je peux avoir plusieurs fois le même CODE et la même REF

[Dendrite]

Petite remarque au passage... Je ne sais pas ce qu'il y a sur ta table requete2, mais tu n'aurais pas aussi intérêt à filtrer sur 2016 ?

Code SQL :

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

LEFT OUTER JOIN `requete2` ON  YEAR(requete2.`date`)=2016 AND `requete2`.`ARTICLE` = `requete`.`ARTICLE`

En terme de performance, ça peut jouer. Je m'explique. La base de données lit les informations dans l'ordre.
Si tu veux faire remonter dans une table qui stocke les personnels de toute la France, les femmes de Créteil...

On pense au premier abord que ces 2 requêtes sont identiques ?

Code SQL :

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

1
2
3
SELECT civ,nom,prenom,naissance
FROM employe
WHERE cp="94000" and civ="Mme"

Code SQL :

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

1
2
3
4
 
SELECT civ,nom,prenom,naissance
FROM employe
WHERE  civ="Mme" AND cp="94000"

Eh bien raté ! La première est bien plus performante que la 2nde, si on avait des centaines de milliers de données dans la table. Toujours mettre le plus discriminant, le plus filtrant en premier !
La requête 1 fera dans l'ordre : remonter les 20 000 employés de Créteil puis checkera le sexe de chacun...
L'autre... fait remonter les 500 000 femmes de France puis... checkera pour ces 500 000 lesquelles vivent à Créteil...
On voit tout de suite que le boulot n'est pas le même, même pour une machine aux performances d'enfer.

[Arkyano]

Si les données sont de début 2016, j'ai besoin de récupérer les dernières valeurs en date, donc ça peut être fin 2015
mais ça aurait pu en effet

Eh bien raté ! La première est bien plus performante que la 2nde, si on avait des centaines de milliers de données dans la table. Toujours mettre le plus discriminant, le plus filtrant en premier !
La requête 1 fera dans l'ordre : remonter les 20 000 employés de Créteil puis checkera le sexe de chacun...
L'autre... fait remonter les 500 000 femmes de France puis... checkera pour ces 500 000 lesquelles vivent à Créteil...
On voit tout de suite que le boulot n'est pas le même, même pour une machine aux performances d'enfer.

Ah oui vu comme ça c'est logique

[Dendrite]

Alors, attention, pour l'ordre des WHERE, je te répète les explications d'un stage DB2 que j'avais eu, avec un vieux formateur passionnant.
mais SQLPRO (aka Frédéric Brouard, LA pointure) dit que c'est n'importe quoi... sauf pour mysql et postgresql...

https://www.developpez.net/forums/d1...ortant-2012-a/

2 questions car je suis une petite curieuse :

Qui a modélisé cette base qui comporte des centaines de milliers de données ? Un bon professionnel expérimenté en base de données j'imagine ?
A moins que ça ne soit de la reprise de données conçues ailleurs ?
Et du coup qui a fait la requête que tu nous montres ?

Si les données sont de début 2016, j'ai besoin de récupérer les dernières valeurs en date, donc ça peut être fin 2015

Tu peux revenir en termes humains, de la même façon, sur ce que fait précisément ta requête ? Que stocke requete, requete2, que cherchent à faire requeter et requetef ?
Avant optimisation, combien de temps tourne ta requête ?

[Celira]

Au départ, tu parles d'un script PHP, mais tu ne montres que du SQL. Tu as testé la requête seule (genre dans PhpMyAdmin) pour vérifier que c'est bien le goulot d'étranglement dans ton script ? (ce serait bête qu'on se prenne la tête sur la requête alors que c'est le reste du script qui pédale dans la semoule )

[Arkyano]

Bonjour,

Au départ, tu parles d'un script PHP, mais tu ne montres que du SQL. Tu as testé la requête seule (genre dans PhpMyAdmin) pour vérifier que c'est bien le goulot d'étranglement dans ton script ? (ce serait bête qu'on se prenne la tête sur la requête alors que c'est le reste du script qui pédale dans la semoule )

dans mon php, plutôt classique, j'exporte tout dans un tableau avec mysql_fetch_assoc/implode dans une boucle while, il fonctionne très bien avec d'autres requêtes.
la requête sql en elle même prend du temps, c'est testé

Les fonctionnes YEAR et MONTH+LPAD fonctionne parfaitement, je me suis vraiment cassé la tête pour rien ...

Code :

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

1
2
3
4
5
6
7
8
9
10
select distinct requeter.`ARTICLE` as article_r,requeter.R, requeter.`DATE` as date_r 
from requete
left outer join `requeter` ON 
`requeter`.`ARTICLE` = `requete`.`ARTICLE`
AND  `requeter`.`DATE` =(
	SELECT MAX(subqr.`DATE`)
	from `requeter` subqr
	where  subqr.`ARTICLE` = `requete`.`ARTICLE`
	and subqr.`DATE`<= `requete`.`DATE`
)

J'ai bien le résultat pas de soucis en filtrant sur ARTICLE mais je dois encore essayer sur une sélection plus large, par contre, j'ai testé avec les tables temporaires pour toutes mes tables filles, ça s'exécute, mais ça prend toujours du temps. J'ai ajouté des index sur mes dates, pas beaucoup mieux non plus.
faut que je regarde du côté des clés maintenant afin de lier mes tables entre elles ... ou je fait fausse route ?

Qui a modélisé cette base qui comporte des centaines de milliers de données ? Un bon professionnel expérimenté en base de données j'imagine ?
A moins que ça ne soit de la reprise de données conçues ailleurs ?
Et du coup qui a fait la requête que tu nous montres ?

c'est de la reprise de données que j'ai ajouté dans des tables mysql pour plus de facilité/commodité.
Le but est de récupérer le résultat de mes tables filles, et de fournir un résultat avec en plus des valeurs de ma table mère, par l'intermédiaire de ma requête principale. la requête montrée est de moi
cela doit récupérer des infos de commande (requete), puis, ajouter une colonne avec le dernier fabriquant en date (requetef) ainsi que le dernier retour en date (requeter), toujours par rapport à la date de commande de la ligne en question.
le tout regroupé par année, mois, ...

merci

[Artemus24]

Salut à tous.

Plusieurs remarques :

1) ici, nous traitons que de MySql et non du php.

2) quelle est la version mysql que vous utilisez ?

3) nous n'avons pas le descriptif de vos tables.
Communiquez-nous les DDL des tables "requete", "requete2", "requeter", "requetef".

4) SQLPRO est un expert dans le domaine des bases de données, donc il sait de quoi il parle !
Autrement dit, Dendrite vous a raconté une connerie en ce qui concerne l'ordre des conditions dans la clause where.
La meilleure façon pour se rendre compte est d'utiliser "explain" !

5) il y a un problème avec les dates :

pour les dates, elles sont de base, aux formats 00/00/0000 00h:00m:00s, ça ira ?

Et bien non, ça ne va pas car votre colonne date a été déclaré en varchar().
Il existe un type "datetime" dans MySql, pourquoi ne pas l'utiliser ?
Le type de la colonne doit être "datetime" et le format doit se présenter ainsi : "YYYY-MM-DD HH:MM:SS".

6) arrêtez de mettre partout des parenthèses dans la clause where.
Cela ne sert à rien, sinon à alourdir votre requête.

7) utilisez des alias dans votre requête, cela permet d'alléger son écriture et sa compréhension.
t1 --> requete
t2 --> requete2
t3 --> requeter
t4 --> requetef

8) la clause suivante :

Code :

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

where  1=1

ne sert à rien dans MySql. C'est une astuce qui vient du DB2.

9) il y a plus simple que de procéder ainsi :

Code :

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

1
2
3
4
5
SELECT            date_format((LEFT(`requete`.DATE, 10)), '%Y'),
                  date_format((LEFT(`requete`.DATE, 10)), '%m'),
...
       GROUP BY  date_format((LEFT(`requete`.DATE, 10)), '%Y'),
                 date_format((LEFT(`requete`.DATE, 10)), '%m'),

Vous désirez regrouper vos lignes selon l'année et le mois, d'où :

Code :

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

1
2
3
select date_format(`date`, '%Y-%m') as periode
...
group by periode

Cela nécessite au préalable d'avoir votre colonne 'date' dans le type 'datetime' comme je vous l'ai indiqué au §4).

10) le "group by" doit se faire sur toutes les colonnes qui ne subissent pas une agrégation.
Or, dans le select, vous avez des colonnes qui sont sans agrégation (t2.AC, t3.R et t4.F).
Soit deux choses l'une, ou bien vous les ajoutez dans le "group by" où bien vous créez une sous-requête uniquement pour la table "requete".

11) là,je crains le pire :

Je verrai aussi pour les clés primaires si toujours utiles sauf que je peux avoir plusieurs fois le même CODE et la même REF

Dois-je comprendre qu vous n'utilisez pas des clefs primaires, voire même des clefs étrangères dans vos tables ?
Et qu'en est-il des index ?

12) j'ai reformulé votre requête :

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
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
SELECT           t1.periode,
                 t1.CODE,
                 t1.PAYS,
                 t1.REF,
                 t1.STATUT,
                 t1.SECTEUR,
                 t1.DEVISE,
                 t1.QTE,
                 t1.PB,
                 t1.PN,
 
                 t2.AC,
                 t3.R,
                 t4.F,
 
                 t2.AC * t1.QTE a total
 
           from  (  SELECT  date_format(DATE,'%Y-%m') as periode,
                            CODE,
                            PAYS,
                            REF,
                            STATUT,
                            SECTEUR,
                            DEVISE,
                            sum(QTE) as QTE,
                            sum(PB)  as PB,
                            sum(PN)  as PN
 
                      FROM  `requete`
 
                     WHERE  year(date)= 2016
 
                  GROUP BY  periode,
                            CODE,
                            PAYS,
                            REF,
                            STATUT,
                            SECTEUR,
                            DEVISE
                 ) as t1
 
LEFT OUTER JOIN  `requete2` as t2
             ON  t2.REF = t1.REF
 
LEFT OUTER JOIN  requeter as t3
             ON  t3.REF  = t1.REF
            AND  t3.DATE = (  SELECT  MAX(ta.DATE)
                                from  requeter as ta
                               where  ta.REF   = t1.REF
                                 and  ta.DATE <= t1.DATE
                           )
 
LEFT OUTER JOIN  requetef as t4
             ON  t4.REF  = t1.REF
            AND  t4.DATE = (  SELECT  MAX(tb.DATE)
                                from  requetef as tb
                               where  tb.REF   = t1.REF
                                 and  tb.DATE <= t1.DATE
                           );

13) pour la performance, vous devez créer des index sur les clauses where. Par exemple :

Code :

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

WHERE  year(date)= 2016

Il faut un index dans la table "requete" sur la colonne "date" qui est dans le type "datetime".

Code :

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

ON  t2.REF = t1.REF

Il faut un index dans la table "requete2" sur la colonne "ref".

Code :

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

1
2
where  ta.REF   = t1.REF
  and  ta.DATE <= t1.DATE

Il faut un index dans la table "requeter" sur les colonnes "date" et "ref".

Code :

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

1
2
where  tb.REF   = t1.REF
  and  tb.DATE <= t1.DATE

Il faut un index dans la table "requetef" sur les colonnes "date" et "ref".

14) afin de vérifier si les index ont bien été sélectionnées, vous devez utiliser "explain" que vous mettrez juste avant le select.
Le tableau que vous obtiendrez devra indiquer les choix que mysql aura fait pour les performances.

@+

[Arkyano]

un grand merci pour toutes vos explications, je comprends mieux certaines choses.
De ce fait, cette semaine, j'ai revu mes requêtes, tables, etc ...

je peux confirmer que ma requête était longue même sans php et ma version de MySQL est la 5.6.
J'ai modifié tous les varchar de dates par des champs datetime et date, selon le cas.
J'ai placer tous les indexes comme indiqué dans votre message, sans utiliser de clés pour le moment.
J'ai enlevé les 1=1 et parenthèses inutiles ... et reformulé ma requête comme indiqué.

j'arrive à avoir un résultat, mais le temps est quand même plutôt long, quand je fais un explain de ma requête, je vois bien qu'elle utilise mes index, j'ai l'impression qu'il n'utilise pas l'index sur le champ "date" de la table "requete/t1" :

13) pour la performance, vous devez créer des index sur les clauses where. Par exemple :
Code : Sélectionner tout - Visualiser dans une fenêtre à part
WHERE year(date)= 2016
Il faut un index dans la table "requete" sur la colonne "date" qui est dans le type "datetime".

dans la colonne "REF" de mon explain, j'ai juste t1.REF ... je ne vois pas t1.DATE
merci

[Artemus24]

Salut Arkyano.

j'ai l'impression qu'il n'utilise pas l'index sur le champ "date" de la table "requete/t1" :

Dans ce cas, n'utilisez pas la fonction "year()", mais écrivez plutôt :

Code :

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

WHERE date between '2016-01-01' and '2016-12-31'

dans la colonne "REF" de mon explain, j'ai juste t1.REF ... je ne vois pas t1.DATE

Vous avez dû mettre un index sur une seul colonne, la colonne t1.ref.
Mettez plutôt ces déclaratives :

Code :

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

1
2
CREATE UNIQUE INDEX `idx` USING BTREE ON `requeter` (`ref`,`date`);
CREATE UNIQUE INDEX `idx` USING BTREE ON `requetef` (`ref`,`date`);

@+

[Arkyano]

Dans ce cas, n'utilisez pas la fonction "year()", mais écrivez plutôt :

Code :

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

WHERE date between '2016-01-01' and '2016-12-31'

c'est testé, alors avec votre suggestion, j'arrive à voir mon index de date dans le type "Derived" avec pour extra : 'Using where; Using temporary; Using filesort'

Vous avez dû mettre un index sur une seul colonne, la colonne t1.ref.
Mettez plutôt ces déclaratives :

Code :

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

1
2
CREATE UNIQUE INDEX `idx` USING BTREE ON `requeter` (`ref`,`date`);
CREATE UNIQUE INDEX `idx` USING BTREE ON `requetef` (`ref`,`date`);

j'ai effectivement mis l'index sur une seule colonne pour la requête t1, mais c'était ma colonne date , serait-il intéressant de mettre un index sur REF aussi ?
pour les autres tables, j'ai mis les index sur Ref et date

merci

[Artemus24]

Salut Arkyano.

serait-il intéressant de mettre un index sur REF aussi ?

Pour votre requête, non, car le where se porte sur vos deux colonnes REF et DATE.
Ne pas oublier qu'un seul index est utilisé par table dans votre requête.

@+

[Arkyano]

Bonjour

Pour votre requête, non, car le where se porte sur vos deux colonnes REF et DATE.
Ne pas oublier qu'un seul index est utilisé par table dans votre requête.

Je ne comprend pas bien,dans le where de ma requête t1, je n'ai que la date (2016), c'est pour ça ?
par contre, dans mes jointures (pour les tables t4 et t5), j'utilise REF et DATE, d'où le besoin de mettre ces index sur REF et DATE dans les tables t4 et t5, mais pas nécessairement dans t1 donc ?

encore merci

[Artemus24]

Salut Arkyano.

Je ne comprends pas bien,dans le where de ma requête t1, je n'ai que la date (2016), c'est pour ça ?

T1, c'est la table "requete". Or la clause where consernant votre table "requete" se porte que sur la colonne "date".
Il est donc inutile d'ajouter la colonne "REF".

Je me suis peut-être mal exprimé, mais je disais qu'il est inutile d'ajouter un second index sur la colonne "ref" sachant qu'il existe déjà un index sur la colonne "DATE".
Si la clause where se porte sur deux colonnes, vous devez créer un index contenant les deux colonnes et non créer deux index se reportant l'une sur "DATE" et l'autre sur "REF".

par contre, dans mes jointures (pour les tables t4 et t5), j'utilise REF et DATE, d'où le besoin de mettre ces index sur REF et DATE dans les tables t4 et t5,

Oui, un index contenant les deux colonnes, comme dans le dernier exemple que je vous ai communiqué.

mais pas nécessairement dans t1 donc ?

Vous avez deux tables, l'une se nomme "in" et l'autre se nomme "out".
Vous reliez vos deux tables par une jointure, sachant que le "from" concerne la table "in" et le "inner join" concerne la table "out".
Pour chaque ligne de la table "in" vous allez faire une recherche sur la table "out".
Vous comprenez qu'il est inutile de mettre un index sur la table "in", puisque vous devez balayer toutes les lignes de cette table.
Inversement, vous devez récupérer que les lignes de la table "out" qui sont en relation avec la table "in".
De ce fait, vous devez mettre un index sur les colonnes de la jointure de la table "out".

Dans votre exemple, vous utilisez une restriction (clause where) sur la table "in".
Il est nécessaire de mettre un index sur les colonnes de la clause "where" de la table "in"
De ce fait, on ne balaye pas tous les lignes de la table "in" mais uniquement les lignes de l'index ayant l'égalité dans la clause where.

@+