Discussion :

[aurelienl]

Bonjour,

J'ai un soucis avec une requete qui me retourne une forte volumetrie, je travaille dessus depuis 3 jours et impossible de reduire sa durée d'execution.

La voici:

Code :

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

1
2
3
4
5
6
7
8
9
10
11
12
 
SELECT m.media_id, s.action_id, ad.date_a, rd.date_r, m.release_date, an.nb_a, rn.nb_r, at.is_pro, IFNULL(IFNULL(tarif.tarif, m.price / 100 * 0.45), 99999999) AS prix
FROM mediatheque AS m 
JOIN type_action AS s
LEFT JOIN mediacat AS mc ON m.media_id = mc.media_id
LEFT JOIN ( SELECT a.media_id, aag.action_id, MAX(aag.date) AS date_a FROM annonces AS a INNER JOIN action_annonce_groupe AS aag ON a.ann_id = aag.ann_id WHERE  aag.date < NOW() AND a.media_id IS NOT NULL AND a.deleted = 0 AND a.ann_visible = 1 GROUP BY a.media_id, aag.action_id ) as ad ON ad.media_id = m.media_id AND ad.action_id = s.action_id
LEFT JOIN (  SELECT r.media_id, ra.action_id, MAX(ra.date) AS date_r FROM recherche AS r INNER JOIN recherche_action AS ra ON r.recherche_id = ra.recherche_id AND ra.date < NOW() GROUP BY r.recherche_id, action_id ) as rd ON rd.media_id = m.media_id AND rd.action_id = s.action_id
LEFT JOIN ( SELECT a.media_id, COUNT(a.ann_id) AS nb_a FROM annonces AS a WHERE a.media_id IS NOT NULL AND a.deleted = 0 AND a.ann_visible = 1 GROUP BY a.media_id ) AS an ON an.media_id = m.media_id
LEFT JOIN ( SELECT r.media_id, COUNT(r.recherche_id) AS nb_r FROM recherche AS r WHERE r.media_id IS NOT NULL GROUP BY r.media_id ) AS rn ON rn.media_id = m.media_id
LEFT JOIN( SELECT a.media_id, aag.action_id, MIN(aag.tarif) AS tarif FROM annonces AS a INNER JOIN action_annonce_groupe AS aag ON a.ann_id = aag.ann_id  WHERE a.media_id IS NOT NULL AND a.deleted = 0 AND a.ann_visible = 1 GROUP BY a.media_id, aag.action_id) AS tarif ON tarif.media_id = m.media_id AND tarif.action_id = s.action_id
LEFT JOIN (SELECT a.media_id, u.is_pro FROM annonces AS a INNER JOIN users AS u ON a.user_id = u.user_id WHERE a.media_id IS NOT NULL AND a.deleted = 0 AND a.ann_visible = 1 ) AS at ON at.media_id = m.media_id
GROUP BY m.media_id, s.action_id

Cette Requete ne sera pas utilisé de cette facon, les left join avec sous requete ne seront utilisé qu'une seule a la fois pour me permettre de trier le resultat.

Voici le resultat du EXPLAIN en format csv (je ne sais pas comment la présenter autrement):

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
"id","select_type","table","type","possible_keys","key","key_len","ref","rows","Extra"
1,"PRIMARY","s","index","","PRIMARY","4","",6,"Using index; Using temporary; Using filesort"
1,"PRIMARY","m","ALL","","","","",780195,""
1,"PRIMARY","mc","ref","IDX_mediacat_media_id","IDX_mediacat_media_id","4","nx14.m.media_id",1,"Using index"
1,"PRIMARY","<derived2>","ALL","","","","",14,""
1,"PRIMARY","<derived3>","ALL","","","","",23,""
1,"PRIMARY","<derived4>","ALL","","","","",13,""
1,"PRIMARY","<derived5>","ALL","","","","",13,""
1,"PRIMARY","<derived6>","ALL","","","","",14,""
1,"PRIMARY","<derived7>","ALL","","","","",31,""
7,"DERIVED","u","ALL","PRIMARY","","","",18,""
7,"DERIVED","a","ref","IDX_annonces_user,IDX_annonces_media","IDX_annonces_user","4","nx14.u.user_id",1,"Using where"
6,"DERIVED","a","ALL","PRIMARY,IDX_annonces_media","","","",22,"Using where; Using temporary; Using filesort"
6,"DERIVED","aag","ref","IDX_action_annonce_groupe_ann","IDX_action_annonce_groupe_ann","8","nx14.a.ann_id",1,""
5,"DERIVED","r","index","IDX_usersmediatheque_media_id","IDX_usersmediatheque_media_id","4","",23,"Using where; Using index"
4,"DERIVED","a","index","IDX_annonces_media","IDX_annonces_media","5","",22,"Using where"
3,"DERIVED","r","index","PRIMARY","IDX_usersmediatheque_media_id","4","",23,"Using index; Using temporary; Using filesort"
3,"DERIVED","ra","ref","IDX_search_action_recherche,IDX_search_action_date","IDX_search_action_recherche","8","nx14.r.recherche_id",1,"Using where"
2,"DERIVED","a","ALL","PRIMARY,IDX_annonces_media","","","",22,"Using where; Using temporary; Using filesort"
2,"DERIVED","aag","ref","IDX_action_annonce_groupe_ann,IDX_action_annonce_groupe_date","IDX_action_annonce_groupe_ann","8","nx14.a.ann_id",1,"Using where"

J'ai essayer de mettre des index ce qui a eu pour effet de ralentir la requete... J'ai lu un peu tous ce qu'on peu trouver sur le net a propos des index couvrant et a mon avis je n'ai pas su les utiliser correctement.

Si quelqu'un peu m'aider ça me sauverai la vie. merci.

Ps: la config config du serveur utiliser pour les test:

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
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
 
#
# The MySQL database server configuration file.
#
# You can copy this to one of:
# - "/etc/mysql/my.cnf" to set global options,
# - "~/.my.cnf" to set user-specific options.
# 
# One can use all long options that the program supports.
# Run program with --help to get a list of available options and with
# --print-defaults to see which it would actually understand and use.
#
# For explanations see
# http://dev.mysql.com/doc/mysql/en/server-system-variables.html
 
# This will be passed to all mysql clients
# It has been reported that passwords should be enclosed with ticks/quotes
# escpecially if they contain "#" chars...
# Remember to edit /etc/mysql/debian.cnf when changing the socket location.
[client]
port		= 3306
socket		= /var/run/mysqld/mysqld.sock
 
# Here is entries for some specific programs
# The following values assume you have at least 32M ram
 
# This was formally known as [safe_mysqld]. Both versions are currently parsed.
[mysqld_safe]
socket		= /var/run/mysqld/mysqld.sock
nice		= 0
 
[mysqld]
#
# * Basic Settings
#
user		= mysql
pid-file	= /var/run/mysqld/mysqld.pid
socket		= /var/run/mysqld/mysqld.sock
port		= 3306
basedir		= /usr
datadir		= /var/lib/mysql
tmpdir		= /tmp
language	= /usr/share/mysql/english
skip-external-locking
#
# Instead of skip-networking the default is now to listen only on
# localhost which is more compatible and is not less secure.
bind-address		= 0.0.0.0
#
# * Fine Tuning
#
key_buffer		= 16M
max_allowed_packet	= 16M
thread_stack		= 128K
thread_cache_size	= 8
# This replaces the startup script and checks MyISAM tables if needed
# the first time they are touched
myisam-recover		= BACKUP
max_connections        = 50
#table_cache            = 64
#thread_concurrency     = 10
#
# * Query Cache Configuration
#
query_cache_limit       = 1M
query_cache_size        = 16M
#
# * Logging and Replication
#
# Both location gets rotated by the cronjob.
# Be aware that this log type is a performance killer.
#log		= /var/log/mysql/mysql.log
#
# Error logging goes to syslog. This is a Debian improvement :)
#
# Here you can see queries with especially long duration
log_slow_queries	= /var/log/mysql/mysql-slow.log
long_query_time = 2
#log-queries-not-using-indexes
#
# The following can be used as easy to replay backup logs or for replication.
# note: if you are setting up a replication slave, see README.Debian about
#       other settings you may need to change.
#server-id		= 1
#log_bin			= /var/log/mysql/mysql-bin.log
expire_logs_days	= 10
max_binlog_size         = 100M
#binlog_do_db		= include_database_name
#binlog_ignore_db	= include_database_name
#
# * BerkeleyDB
#
# Using BerkeleyDB is now discouraged as its support will cease in 5.1.12.
skip-bdb
#
# * InnoDB
#
# InnoDB is enabled by default with a 10MB datafile in /var/lib/mysql/.
# Read the manual for more InnoDB related options. There are many!
# You might want to disable InnoDB to shrink the mysqld process by circa 100MB.
#skip-innodb
#
# * Security Features
#
# Read the manual, too, if you want chroot!
# chroot = /var/lib/mysql/
#
# For generating SSL certificates I recommend the OpenSSL GUI "tinyca".
#
# ssl-ca=/etc/mysql/cacert.pem
# ssl-cert=/etc/mysql/server-cert.pem
# ssl-key=/etc/mysql/server-key.pem
innodb_buffer_pool_size = 512M
 
sort_buffer_size=12M
read_buffer_size=12M
read_rnd_buffer_size=12M
 
ft_min_word_len=2
ft_stopword_file = /etc/stopwords.txt
 
 
[mysqldump]
quick
quote-names
max_allowed_packet	= 16M
 
[mysql]
#no-auto-rehash	# faster start of mysql but no tab completition
 
[isamchk]
key_buffer		= 16M
 
#
# * NDB Cluster
#
# See /usr/share/doc/mysql-server-*/README.Debian for more information.
#
# The following configuration is read by the NDB Data Nodes (ndbd processes)
# not from the NDB Management Nodes (ndb_mgmd processes).
#
# [MYSQL_CLUSTER]
# ndb-connectstring=127.0.0.1
 
 
#
# * IMPORTANT: Additional settings that can override those from this file!
#   The files must end with '.cnf', otherwise they'll be ignored.
#
!includedir /etc/mysql/conf.d/

[CinePhil]

Je ne comprends pas cette partie de ton message :

Cette Requete ne sera pas utilisé de cette facon, les left join avec sous requete ne seront utilisé qu'une seule a la fois pour me permettre de trier le resultat.

Tu veux dire que la requête réellement utilisée n'a qu'un LEFT JOIN selon le contexte ?

Sinon sur la requête, il manque la condition de jointure après

Code :

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

JOIN type_action AS s

Je reproduis ci-dessous une partie remise en forme :

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
FROM mediatheque AS m 
JOIN type_action AS s
LEFT JOIN mediacat AS mc ON m.media_id = mc.media_id
LEFT JOIN ( 
  SELECT a.media_id, aag.action_id, 
    MAX(aag.date) AS date_a 
  FROM annonces AS a 
  INNER JOIN action_annonce_groupe AS aag ON a.ann_id = aag.ann_id 
  WHERE  aag.date < NOW() 
    AND a.media_id IS NOT NULL 
    AND a.deleted = 0 
    AND a.ann_visible = 1 
  GROUP BY a.media_id, aag.action_id 
) AS ad ON ad.media_id = m.media_id 
  AND ad.action_id = s.action_id

Il n'y a qu'une instance de la table dérivée ad générée par la sous-requête mais celle-ci a une condition de jointure avec m et une autre avec s.
J'ai de gros doutes sur le fonctionnement correct d'une telle syntaxe.

Le GROUP BY de la requête principale ne porte que sur deux colonnes alors qu'il y en a 8 ne faisant pas l'objet d'une fonction d'aggrégation. Les données affichées par les 6 autres seront donc aléatoires.

Bref, cette requête ne donne probablement pas le résultat attendu.

Si tu nous donnais un schéma et/ou la structure des tables impliquées et une explication du besoin auquel est censé répondre cette requête, on pourrait plus facilement chercher une solution simplifiée. Là c'est vraiment complexe pour en saisir le sens.

[aurelienl]

En fait oui J'ai des elements dans ma table mediatheque qui seront filtré en fonction d'element contenus dans les autre table (6 filtres: 6jointures)
donc je n'aurais qu'une jointure a la fois.

typiquement voila une des requete:

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 SQL_CALC_FOUND_ROWS m.media_id , IFNULL(IFNULL(tarif.tarif, m.price / 100 * 0.45), 99999999) AS prix , m.release_date 
FROM mediatheque AS m 
INNER JOIN mediacat AS mc 
ON m.media_id = mc.media_id AND mc.cat_id IN (122, 41, 66, 123, 124, 125) 
LEFT JOIN(
  SELECT a.media_id, MIN(aag.tarif) AS tarif 
  FROM annonces AS a 
  INNER JOIN action_annonce_groupe AS aag 
  ON a.ann_id = aag.ann_id 
  WHERE a.media_id IS NOT NULL AND aag.action_id = 1 AND a.deleted = 0 AND a.ann_visible = 1 GROUP BY a.media_id 
) AS tarif 
ON tarif.media_id = m.media_id 
GROUP BY m.media_id 
ORDER BY prix ASC, m.release_date DESC 
LIMIT 0, 9

J'ai legerement modifier la requete. (la valeur du "mc.cat_id IN (" contient bien plus d'id)

le probleme c'est que cette requete mets plus de 15 secondes a s'executer...

Voila de nouveau le explain de cette requete en csv:

Code :

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

1
2
3
4
5
6
7
 
"id","select_type","table","type","possible_keys","key","key_len","ref","rows","Extra"
1,"PRIMARY","m","ALL","PRIMARY","","","",880293,"Using temporary; Using filesort"
1,"PRIMARY","mc","ref","PRIMARY,IDX_mediacat_media_id","IDX_mediacat_media_id","4","nx14.m.media_id",1,"Using where; Using index"
1,"PRIMARY","<derived2>","ALL","","","","",13,""
2,"DERIVED","a","index","PRIMARY,IDX_annonces_media","IDX_annonces_media","5","",22,"Using where"
2,"DERIVED","aag","ref","PRIMARY,IDX_action_annonce_groupe_ann","PRIMARY","12","nx14.a.ann_id",1,""

voici la description des tables utilisé ci dessus:
mediatheque (media_id INT, release_date DATETIME) PRIMARY(media_id)
mediacat(media_id INT, cat_id INT) PRIMARY (media_id, cat_id)
annonce(ann_id INT, media_id INT, deleted TINYINT, visible TINYINT) PRIMARY(ann_id)
action_annonces_groupes(action_id INT, ann_id INT, groupe_id INT, tarif INT) PRIMARY(action_id, ann_id, groupe_id)

Voila j'espère avoir donner assez d'infos...

[CinePhil]

Les tables sont-elles correctement indexées ?
Notamment la table action_annonce_groupe a t-elle un index sur ann_id et sur groupe_id ?

Suggestion :
Transporter ce qui suit dans un WHERE de la requête générale :

Code :

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

mc.cat_id IN (122, 41, 66, 123, 124, 125)

Le GROUP BY devrait être sur les deux colonnes ne faisant pas l'objet d'une fonction de regroupement :

Code :

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

GROUP BY m.media_id, m.release_date

[aurelienl]

J'ai apporter à ma requete les modification sugeré qui n'ont eu aucune influence sur le temps d'execution.

Pour ce qui est des index, voici ceux qui sont en place:

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
60
 
CREATE TABLE  `nx14`.`mediatheque` (
  `media_id` bigint(20) NOT NULL,
  `release_date` date default NULL,
  `price` int(11) default NULL,
  PRIMARY KEY  (`media_id`),
  KEY `new_index_olfdnb` (`release_date`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci
 
CREATE TABLE  `nx14`.`mediacat` (
  `cat_id` int(10) unsigned NOT NULL,
  `media_id` int(10) unsigned NOT NULL,
  PRIMARY KEY  (`cat_id`,`media_id`),
  KEY `IDX_mediacat_media_id` (`media_id`),
  CONSTRAINT `fk_mediacat_cat_id` FOREIGN KEY (`cat_id`) REFERENCES `categories` (`cat_id`) ON UPDATE CASCADE,
  CONSTRAINT `fk_mediacat_media_id` FOREIGN KEY (`media_id`) REFERENCES `mediatheque` (`media_id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci
 
CREATE TABLE  `nx14`.`annonces` (
  `ann_id` bigint(20) unsigned NOT NULL,
  `ann_title` varchar(127) collate utf8_unicode_ci NOT NULL,
  `ann_text` text collate utf8_unicode_ci NOT NULL,
  `ann_creation_date` datetime NOT NULL,
  `ann_modification_date` datetime default NULL,
  `ann_visible` tinyint(1) NOT NULL default '1',
  `user_id` int(10) unsigned NOT NULL,
  `etat` enum('neuf','use','abime','casse') collate utf8_unicode_ci default NULL,
  `media_id` int(10) unsigned default NULL,
  `langues_id` int(10) unsigned NOT NULL default '1',
  `deleted` tinyint(1) NOT NULL default '0',
  `old_id` int(11) default NULL,
  `ville_id` int(10) unsigned default NULL,
  PRIMARY KEY  (`ann_id`),
  KEY `IDX_annonces_user` (`user_id`),
  KEY `IDX_annonces_media` (`media_id`),
  KEY `IDX_annonces_langue` (`langues_id`),
  KEY `FK_annonces_ville` (`ville_id`),
  CONSTRAINT `FK_annonces_langue` FOREIGN KEY (`langues_id`) REFERENCES `langues` (`langues_id`) ON UPDATE CASCADE,
  CONSTRAINT `FK_annonces_media` FOREIGN KEY (`media_id`) REFERENCES `mediatheque` (`media_id`) ON DELETE SET NULL ON UPDATE CASCADE,
  CONSTRAINT `FK_annonces_user` FOREIGN KEY (`user_id`) REFERENCES `users` (`user_id`) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `FK_annonces_ville` FOREIGN KEY (`ville_id`) REFERENCES `ville` (`ville_id`) ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci
 
CREATE TABLE  `nx14`.`action_annonce_groupe` (
  `action_id` int(10) unsigned NOT NULL,
  `ann_id` bigint(20) unsigned NOT NULL,
  `groupe_id` bigint(20) unsigned NOT NULL,
  `tarif` int(11) default NULL,
  `nb_vues` int(11) NOT NULL default '0',
  `date` date NOT NULL,
  `negociable` tinyint(1) NOT NULL default '0',
  `livraison` varchar(20) collate utf8_unicode_ci NOT NULL,
  PRIMARY KEY  (`action_id`,`ann_id`,`groupe_id`),
  KEY `IDX_action_annonce_groupe_ann` (`ann_id`),
  KEY `IDX_action_annonce_groupe_groupe` (`groupe_id`),
  KEY `IDX_action_annonce_groupe_date` (`date`),
  CONSTRAINT `FK_action_annonce_groupe_action` FOREIGN KEY (`action_id`) REFERENCES `type_action` (`action_id`) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `FK_action_annonce_groupe_ann` FOREIGN KEY (`ann_id`) REFERENCES `annonces` (`ann_id`) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `FK_action_annonce_groupe_groupe` FOREIGN KEY (`groupe_id`) REFERENCES `groupes` (`groupe_id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci

J'ai déjà essyer de mette plus d'index (en particulier des index couvrant comme expliquer ici http://sqlpro.developpez.com/cours/quoi-indexer/), mais les seul resultat que j'ai obtenu, c'est une augmentation du temps d'execution.
Je n'ai pas compris exactement comment je devais les appliquer avec mes sous requete...

[CinePhil]

Les index semblent OK.
On peut avoir une idée de la volumétrie des données (nombre de lignes à traiter) ?
Ce qui suit me laisse à penser que le serveur ne traite pas tout en mémoire :

1,"PRIMARY","m","ALL","PRIMARY","","","",880293,"Using temporary; Using filesort"

Il y a 880293 lignes traitées dans la table mediatheque.
Le serveur est-il suffisamment puissant ?

Je vois aussi que le type de l'identifiant media_id est BIGINT alors qu'en tant que clé étrangère dans annonces et dans mediacat, c'est du INT. Ca peut poser problème. réduire le BIGINT à INT (après sauvegarde de la BDD au cas où !) pourrait aider car le BIGINT est codé sur 8 octets alors que le INT est codé sur 4 octets.