PHPfrance

la communauté d'entraide francophone dédiée au PHP

amelioration d'une requette

14 messages   •   Page 1 sur 1
   Outils de sujet
jad
Invité n'ayant pas de compte PHPfrance

22 nov. 2016, 10:36

Salut a tous

j'ai une requette SQL qui met un peu plus de 30 seconde, pourrier vous m'aider a lâmeliorer ?

la reqette : 
SELECT 	tnw_event_control_test_mark.id, tnw_event_control_test_mark.event_control_test_id, tnw_event_control_test_mark.job_description_control_test_mark_id, tnw_event_control_test_mark.checked,
		tnw_event_control_test_mark.corrected, tnw_event_control_test_mark.correction_date, tnw_event_control_test_mark.comment, tnw_room_control_test_mark.coefficient, tnw_room_control_test_mark.position,
        tnw_control_test_mark.name 
FROM tnw_event_control_test_mark 
LEFT JOIN tnw_job_description_control_test_mark ON tnw_event_control_test_mark.job_description_control_test_mark_id = tnw_job_description_control_test_mark.id 
LEFT JOIN tnw_room_control_test_mark ON tnw_job_description_control_test_mark.room_control_test_mark_id = tnw_room_control_test_mark.id 
LEFT JOIN tnw_control_test_mark ON tnw_room_control_test_mark.control_test_mark_id = tnw_control_test_mark.id 
WHERE 	tnw_event_control_test_mark.event_control_test_id BETWEEN 3621 AND 3722 OR 
		tnw_event_control_test_mark.event_control_test_id BETWEEN 4081 AND 4475 OR 
        tnw_event_control_test_mark.event_control_test_id BETWEEN 5199 AND 5657 OR 
        tnw_event_control_test_mark.event_control_test_id BETWEEN 5952 AND 9623 OR 
        tnw_event_control_test_mark.event_control_test_id BETWEEN 9793 AND 9950 OR 
        tnw_event_control_test_mark.event_control_test_id BETWEEN 10808 AND 10935 OR 
        tnw_event_control_test_mark.event_control_test_id BETWEEN 14686 AND 14896 OR 
        tnw_event_control_test_mark.event_control_test_id BETWEEN 19567 AND 19976 OR 
        tnw_event_control_test_mark.event_control_test_id BETWEEN 14897 AND 15254 OR 
        tnw_event_control_test_mark.event_control_test_id BETWEEN 17139 AND 18358 OR 
        tnw_event_control_test_mark.event_control_test_id BETWEEN 18527 AND 18702 OR 
        tnw_event_control_test_mark.event_control_test_id BETWEEN 20365 AND 21293 
ORDER BY tnw_room_control_test_mark.position ASC;
le retour du explain dessu
id : 1
select_type : SIMPLE
table : tnw_event_control_test_mark
type : ALL
possible_keys : event_control_test_id
key : NULL
key_len : NULL
ref : NULL
rows : 45936
Extra : Using where; Using temporary; Using filesort

id : 1
select_type : SIMPLE
table : tnw_job_description_control_test_mark
type : eq_ref
possible_keys : PRIMARY
key : PRIMARY
key_len : 8
ref : topnet.tnw_event_control_test_mark.job_description_control_test_mark_id
rows : 1
Extra : NULL

id : 1
select_type : SIMPLE
table : tnw_room_control_test_mark
type : eq_ref
possible_keys : PRIMARY
key : PRIMARY
key_len : 8
ref : topnet.tnw_job_description_control_test_mark.room_control_test_mark_id
rows : 1
Extra : NULL

id : 1
select_type : SIMPLE
table : tnw_control_test_mark
type : eq_ref
possible_keys : PRIMARY
key : PRIMARY
key_len : 8
ref : topnet.tnw_room_control_test_mark.control_test_mark_id
rows : 1
Extra : NULL
Modifié en dernier par moogli le 22 nov. 2016, 12:15, modifié 1 fois.
Raison : Ajout bbcode
Avatar du membre
Modérateur PHPfrance
Modérateur PHPfrance | 8758 Messages

22 nov. 2016, 12:35

salut,

il y a un index sur tnw_event_control_test_mark.event_control_test_id ? si ce n'est pas le cas c'est la première chose à faire.
idem pour les clefs étrangères.
si tu n'utilises pas les autres table c'est plus rapide ?

sinon il n'y a pas beaucoup d'axe d'amélioration.
C'est quoi le sgbd ?
avec oracle (et peut être postgresql) tu peux, peut être, voir avec la clause with pour limiter les enregistrements sur lequel tu travailles.

Dans tous les cas je dirais qu'il faut faire les jointures en dernier une fois que les données sont triées afin de limiter les calculs et le volume de données (la tu joins les tables et ensuite tu limites le jeux de données.
pour oracle je tenterais cela 
WITH tnw_event AS
  (SELECT tnw_event_control_test_mark.id,
    tnw_event_control_test_mark.event_control_test_id,
    tnw_event_control_test_mark.job_description_control_test_mark_id,
    tnw_event_control_test_mark.checked,
    tnw_event_control_test_mark.corrected,
    tnw_event_control_test_mark.correction_date,
    tnw_event_control_test_mark.comment
  FROM tnw_event_control_test_mark
  WHERE tnw_event_control_test_mark.event_control_test_id BETWEEN 3621 AND 3722
  OR tnw_event_control_test_mark.event_control_test_id BETWEEN 4081 AND 4475
  OR tnw_event_control_test_mark.event_control_test_id BETWEEN 5199 AND 5657
  OR tnw_event_control_test_mark.event_control_test_id BETWEEN 5952 AND 9623
  OR tnw_event_control_test_mark.event_control_test_id BETWEEN 9793 AND 9950
  OR tnw_event_control_test_mark.event_control_test_id BETWEEN 10808 AND 10935
  OR tnw_event_control_test_mark.event_control_test_id BETWEEN 14686 AND 14896
  OR tnw_event_control_test_mark.event_control_test_id BETWEEN 19567 AND 19976
  OR tnw_event_control_test_mark.event_control_test_id BETWEEN 14897 AND 15254
  OR tnw_event_control_test_mark.event_control_test_id BETWEEN 17139 AND 18358
  OR tnw_event_control_test_mark.event_control_test_id BETWEEN 18527 AND 18702
  OR tnw_event_control_test_mark.event_control_test_id BETWEEN 20365 AND 21293
  )
SELECT tnw_event.id,
  tnw_event.event_control_test_id,
  tnw_event.job_description_control_test_mark_id,
  tnw_event.checked,
  tnw_event.corrected,
  tnw_event.correction_date,
  tnw_event.comment,
  tnw_room_control_test_mark.coefficient,
  tnw_room_control_test_mark.position,
  tnw_control_test_mark.name
FROM tnw_event
LEFT JOIN tnw_job_description_control_test_mark
ON tnw_event.job_description_control_test_mark_id = tnw_job_description_control_test_mark.id
LEFT JOIN tnw_room_control_test_mark
ON tnw_job_description_control_test_mark.room_control_test_mark_id = tnw_room_control_test_mark.id
LEFT JOIN tnw_control_test_mark
ON tnw_room_control_test_mark.control_test_mark_id = tnw_control_test_mark.id
ORDER BY tnw_room_control_test_mark.position ASC;
pour mysql 
SELECT tnw_event.id,
  tnw_event.event_control_test_id,
  tnw_event.job_description_control_test_mark_id,
  tnw_event.checked,
  tnw_event.corrected,
  tnw_event.correction_date,
  tnw_event.comment,
  tnw_room_control_test_mark.coefficient,
  tnw_room_control_test_mark.position,
  tnw_control_test_mark.name
FROM
  (SELECT tnw_event_control_test_mark.id,
    tnw_event_control_test_mark.event_control_test_id,
    tnw_event_control_test_mark.job_description_control_test_mark_id,
    tnw_event_control_test_mark.checked,
    tnw_event_control_test_mark.corrected,
    tnw_event_control_test_mark.correction_date,
    tnw_event_control_test_mark.comment
  FROM tnw_event_control_test_mark
  WHERE tnw_event_control_test_mark.event_control_test_id BETWEEN 3621 AND 3722
  OR tnw_event_control_test_mark.event_control_test_id BETWEEN 4081 AND 4475
  OR tnw_event_control_test_mark.event_control_test_id BETWEEN 5199 AND 5657
  OR tnw_event_control_test_mark.event_control_test_id BETWEEN 5952 AND 9623
  OR tnw_event_control_test_mark.event_control_test_id BETWEEN 9793 AND 9950
  OR tnw_event_control_test_mark.event_control_test_id BETWEEN 10808 AND 10935
  OR tnw_event_control_test_mark.event_control_test_id BETWEEN 14686 AND 14896
  OR tnw_event_control_test_mark.event_control_test_id BETWEEN 19567 AND 19976
  OR tnw_event_control_test_mark.event_control_test_id BETWEEN 14897 AND 15254
  OR tnw_event_control_test_mark.event_control_test_id BETWEEN 17139 AND 18358
  OR tnw_event_control_test_mark.event_control_test_id BETWEEN 18527 AND 18702
  OR tnw_event_control_test_mark.event_control_test_id BETWEEN 20365 AND 21293
  ) tnw_event
LEFT JOIN tnw_job_description_control_test_mark
ON tnw_event.job_description_control_test_mark_id = tnw_job_description_control_test_mark.id
LEFT JOIN tnw_room_control_test_mark
ON tnw_job_description_control_test_mark.room_control_test_mark_id = tnw_room_control_test_mark.id
LEFT JOIN tnw_control_test_mark
ON tnw_room_control_test_mark.control_test_mark_id = tnw_control_test_mark.id
ORDER BY tnw_room_control_test_mark.position ASC;
bien entendu l'index sur la colonne event_control_test_id est obligatoire pour aider la chose ;)

@+
Il en faut peu pour être heureux ......
jad
Invité n'ayant pas de compte PHPfrance

22 nov. 2016, 12:55

Merci pour ta réponse

Effectivement les index que tu site existe.

C'est une base mysql, je vais tenter ta seconde requête :-)
jad
Invité n'ayant pas de compte PHPfrance

22 nov. 2016, 13:23

voila le résultat du explaine sur ta requête

id : 1
select_type : PRIMARY
table : <derived2>
type : ALL
possible_keys : NULL
key : NULL
key_len : NULL
ref : NULL
rows : 27835
Extra : Using temporary; Using filesort

id : 1
select_type : PRIMARY
table : tnw_job_description_control_test_mark
type: eq_ref
possible_keys : PRIMARY
key : PRIMARY
key_len : 8
ref : tnw_event.job_description_control_test_mark_id
rows : 1
Extra : NULL

id : 1
select_type : PRIMARY
table : tnw_room_control_test_mark
type: eq_ref
possible_keys : PRIMARY
key : PRIMARY
key_len : 8
ref : topnet.tnw_job_description_control_test_mark.room_control_test_mark_id
rows : 1
Extra : NULL

id : 1
select_type : PRIMARY
table : tnw_control_test_mark
type: eq_ref
possible_keys : PRIMARY
key : PRIMARY
key_len : 8
ref : topnet.tnw_room_control_test_mark.control_test_mark_id
rows : 1
Extra : NULL

id : 2
select_type : DERIVED
table : tnw_event_control_test_mark
type : ALL
possible_keys : event_control_test_id
key : NULL
key_len : NULL
ref : NULL
rows : 45936
Extra : Using where

on n'a pas gagné grand chose voir même rien du tout :'(
Avatar du membre
Modérateur PHPfrance
Modérateur PHPfrance | 8758 Messages

22 nov. 2016, 14:22

tu peux peux être filtrer sur les bornes basses et haute avant d'utiliser les between, tu as quand 27835 lignes, c'est pas énorme mais si tu les digère ensuite en php là c'est long.

sinon tu as vraiment besoin de toutes c'est lignes :-)~

ce qui est étonnant c'est qu'il n'utilise pas d'index sur la sous requête.
key
La colonne key indique l'index que MySQL va décider d'utiliser. Si la clé vaut NULL , aucun index n'a été choisi. Pour forcer MySQL à utiliser un index listé dans la colonne possible_keys , utilisez USE KEY/IGNORE KEY dans votre requête. Syntaxe des SELECT .De plus, exécuter myisamchk --analyze ( Syntaxe d'invocation de myisamchk ) ou ANALYZE TABLE ( Syntaxe de ANALYZE TABLE ) sur la table va aider l'optimiseur à choisir les index.
A priori y a moyen d'indiquer l'index à utiliser avec USE INDEX (event_control_test_id), qui devrait forcer mysql à l'utiliser.
Voici ce que cela donne. 
SELECT tnw_event.id,
  tnw_event.event_control_test_id,
  tnw_event.job_description_control_test_mark_id,
  tnw_event.checked,
  tnw_event.corrected,
  tnw_event.correction_date,
  tnw_event.comment,
  tnw_room_control_test_mark.coefficient,
  tnw_room_control_test_mark.position,
  tnw_control_test_mark.name
FROM
  (SELECT tnw_event_control_test_mark.id,
    tnw_event_control_test_mark.event_control_test_id,
    tnw_event_control_test_mark.job_description_control_test_mark_id,
    tnw_event_control_test_mark.checked,
    tnw_event_control_test_mark.corrected,
    tnw_event_control_test_mark.correction_date,
    tnw_event_control_test_mark.comment
  FROM tnw_event_control_test_mark USE INDEX (event_control_test_id)
  WHERE tnw_event_control_test_mark.event_control_test_id BETWEEN 3621 AND 3722
  OR tnw_event_control_test_mark.event_control_test_id BETWEEN 4081 AND 4475
  OR tnw_event_control_test_mark.event_control_test_id BETWEEN 5199 AND 5657
  OR tnw_event_control_test_mark.event_control_test_id BETWEEN 5952 AND 9623
  OR tnw_event_control_test_mark.event_control_test_id BETWEEN 9793 AND 9950
  OR tnw_event_control_test_mark.event_control_test_id BETWEEN 10808 AND 10935
  OR tnw_event_control_test_mark.event_control_test_id BETWEEN 14686 AND 14896
  OR tnw_event_control_test_mark.event_control_test_id BETWEEN 19567 AND 19976
  OR tnw_event_control_test_mark.event_control_test_id BETWEEN 14897 AND 15254
  OR tnw_event_control_test_mark.event_control_test_id BETWEEN 17139 AND 18358
  OR tnw_event_control_test_mark.event_control_test_id BETWEEN 18527 AND 18702
  OR tnw_event_control_test_mark.event_control_test_id BETWEEN 20365 AND 21293
  ) tnw_event
LEFT JOIN tnw_job_description_control_test_mark
ON tnw_event.job_description_control_test_mark_id = tnw_job_description_control_test_mark.id
LEFT JOIN tnw_room_control_test_mark
ON tnw_job_description_control_test_mark.room_control_test_mark_id = tnw_room_control_test_mark.id
LEFT JOIN tnw_control_test_mark
ON tnw_room_control_test_mark.control_test_mark_id = tnw_control_test_mark.id
ORDER BY tnw_room_control_test_mark.position ASC
si tu fournis les create table + un petit jeux de données (même bidon le tout c'est que ce soit cohérent) cela peux permettre de tester.


@+
Il en faut peu pour être heureux ......
jad
Invité n'ayant pas de compte PHPfrance

22 nov. 2016, 14:42

Le use index ne change rien

je ne comprend pas pourquoi il n'utilise pas cette index ?

En ce qui concerne un jeu de data pour faire des test je vais voir si je peux faire un dump des tables concerné, mais il n'y a pas moyen de joindre de fichier sur un poste ?
Avatar du membre
Modérateur PHPfrance
Modérateur PHPfrance | 8758 Messages

22 nov. 2016, 14:47

utilise l'editeur avancé et tu trouveras un onglet pièce jointe sous les boutons de validation du formulaire ;)
Il en faut peu pour être heureux ......
jad
Invité n'ayant pas de compte PHPfrance

22 nov. 2016, 14:54

désolé, pas moyen de trouver cette option ?
jad
Invité n'ayant pas de compte PHPfrance

22 nov. 2016, 15:00

Code : Tout sélectionner

CREATE TABLE IF NOT EXISTS `tnw_event_control_test_mark` ( `id` bigint(20) NOT NULL AUTO_INCREMENT, `event_control_test_id` bigint(20) NOT NULL, `job_description_control_test_mark_id` bigint(20) NOT NULL, `checked` tinyint(1) NOT NULL, `corrected` tinyint(1) NOT NULL, `correction_date` datetime NOT NULL DEFAULT '0000-00-00 00:00:00', `comment` text NOT NULL, `tnw_create_date` datetime NOT NULL, `tnw_create_user_id` bigint(20) NOT NULL, `tnw_update_date` datetime NOT NULL, `tnw_update_user_id` bigint(20) NOT NULL, PRIMARY KEY (`id`), KEY `event_control_test_id` (`event_control_test_id`), KEY `job_description_control_test_mark_id` (`job_description_control_test_mark_id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=49380 ;

Code : Tout sélectionner

CREATE TABLE IF NOT EXISTS `tnw_job_description_control_test_mark` ( `id` bigint(20) NOT NULL AUTO_INCREMENT, `job_description_control_test_id` bigint(20) NOT NULL, `room_control_test_mark_id` bigint(20) NOT NULL, `tnw_create_date` datetime NOT NULL, `tnw_create_user_id` bigint(20) NOT NULL, `tnw_update_date` datetime NOT NULL, `tnw_update_user_id` bigint(20) NOT NULL, PRIMARY KEY (`id`), KEY `job_description_control_test_id` (`job_description_control_test_id`), KEY `room_control_test_mark_id` (`room_control_test_mark_id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=30101 ;

Code : Tout sélectionner

CREATE TABLE IF NOT EXISTS `tnw_room_control_test_mark` ( `id` bigint(20) NOT NULL AUTO_INCREMENT, `room_control_test_id` bigint(20) NOT NULL, `control_test_mark_id` bigint(20) NOT NULL, `coefficient` float NOT NULL, `actif` tinyint(1) NOT NULL, `position` int(11) NOT NULL, `tnw_create_date` datetime NOT NULL, `tnw_create_user_id` bigint(20) NOT NULL, `tnw_update_date` datetime NOT NULL, `tnw_update_user_id` bigint(20) NOT NULL, PRIMARY KEY (`id`), KEY `room_control_test_id` (`room_control_test_id`), KEY `control_test_mark_id` (`control_test_mark_id`), KEY `position` (`position`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=607396 ;

Code : Tout sélectionner

CREATE TABLE IF NOT EXISTS `tnw_control_test_mark` ( `id` bigint(20) NOT NULL AUTO_INCREMENT, `control_test_id` bigint(20) NOT NULL, `name` varchar(255) NOT NULL, `coefficient` float NOT NULL, `actif` tinyint(1) NOT NULL, `position` int(11) NOT NULL, `tnw_create_date` datetime NOT NULL, `tnw_create_user_id` bigint(20) NOT NULL, `tnw_update_date` datetime NOT NULL, `tnw_update_user_id` bigint(20) NOT NULL, PRIMARY KEY (`id`), KEY `control_test_id` (`control_test_id`), KEY `position` (`position`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=396 ;
Avatar du membre
Modérateur PHPfrance
Modérateur PHPfrance | 8758 Messages

22 nov. 2016, 15:46

je n'avais pas fait attention, réservé aux membres ;)

sinon avec un subselect de plus il passe par l'index 
EXPLAIN
SELECT tnw_event.id,
       tnw_event.event_control_test_id,
       tnw_event.job_description_control_test_mark_id,
       tnw_event.checked,
       tnw_event.corrected,
       tnw_event.correction_date,
       tnw_event.comment,
       tnw_room_control_test_mark.coefficient,
       tnw_room_control_test_mark.position,
       tnw_control_test_mark.name
FROM
  (SELECT *
   FROM
     (SELECT tnw_event_control_test_mark.id,
             tnw_event_control_test_mark.event_control_test_id,
             tnw_event_control_test_mark.job_description_control_test_mark_id,
             tnw_event_control_test_mark.checked,
             tnw_event_control_test_mark.corrected,
             tnw_event_control_test_mark.correction_date,
             tnw_event_control_test_mark.comment
      FROM tnw_event_control_test_mark USE INDEX (event_control_test_id)
      WHERE event_control_test_id<=3621
        AND event_control_test_id <=21293 ) t
   WHERE event_control_test_id BETWEEN 3621 AND 3722
     OR event_control_test_id BETWEEN 4081 AND 4475
     OR event_control_test_id BETWEEN 5199 AND 5657
     OR event_control_test_id BETWEEN 5952 AND 9623
     OR event_control_test_id BETWEEN 9793 AND 9950
     OR event_control_test_id BETWEEN 10808 AND 10935
     OR event_control_test_id BETWEEN 14686 AND 14896
     OR event_control_test_id BETWEEN 19567 AND 19976
     OR event_control_test_id BETWEEN 14897 AND 15254
     OR event_control_test_id BETWEEN 17139 AND 18358
     OR event_control_test_id BETWEEN 18527 AND 18702
     OR event_control_test_id BETWEEN 20365 AND 21293 ) tnw_event
LEFT JOIN tnw_job_description_control_test_mark ON tnw_event.job_description_control_test_mark_id = tnw_job_description_control_test_mark.id
LEFT JOIN tnw_room_control_test_mark ON tnw_job_description_control_test_mark.room_control_test_mark_id = tnw_room_control_test_mark.id
LEFT JOIN tnw_control_test_mark ON tnw_room_control_test_mark.control_test_mark_id = tnw_control_test_mark.id
ORDER BY tnw_room_control_test_mark.position ASC;
id |select_type |table |type |possible_keys |key |key_len |ref |rows |Extra
1 |SIMPLE |tnw_event_control_test_mark |range |event_control_test_id |event_control_test_id |8 |null |1 |Using index condition; Using where; Using temporary; Using filesort
1 |SIMPLE |tnw_job_description_control_test_mark |eq_ref |PRIMARY |PRIMARY |8 |test.tnw_event_control_test_mark.job_description_control_test_mark_id |1 |
1 |SIMPLE |tnw_room_control_test_mark |eq_ref |PRIMARY |PRIMARY |8 |test.tnw_job_description_control_test_mark.room_control_test_mark_id |1 |Using where
1 |SIMPLE |tnw_control_test_mark |eq_ref |PRIMARY |PRIMARY |8 |test.tnw_room_control_test_mark.control_test_mark_id |1 |Using where
reste à voir si c'est utile.

@+
Il en faut peu pour être heureux ......
jad
Petit nouveau ! | 4 Messages

22 nov. 2016, 15:54

La réponse n'est pas identique a la requête initial ?
jad
Petit nouveau ! | 4 Messages

22 nov. 2016, 15:59

petite correction ?
WHERE event_control_test_id<=3621 => WHERE event_control_test_id>=3621
jad
Petit nouveau ! | 4 Messages

22 nov. 2016, 16:06

par contre le explaine dessus me montre que l'index event_control_test_id n'est tjrs pas utilisé
jad
Petit nouveau ! | 4 Messages

22 nov. 2016, 16:14

il faut utiliser FORCE INDEX au lieu de USE INDEX
   Répondre
14 messages   •   Page 1 sur 1
   Outils de sujet