amelioration d'une requette

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
Dernière édition par moogli le 22 Nov 2016, 12:15, édité 1 fois.
Raison : Ajout bbcode

Avatar de l’utilisateur
Modérateur PHPfrance
Modérateur PHPfrance | 8505 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 de l’utilisateur
Modérateur PHPfrance
Modérateur PHPfrance | 8505 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 de l’utilisateur
Modérateur PHPfrance
Modérateur PHPfrance | 8505 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 de l’utilisateur
Modérateur PHPfrance
Modérateur PHPfrance | 8505 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