Index suite à log-queries-not-using-indexes

Bonjour,

J'ai de gros soucis de lenteur sur ma bdd, du coup je recherche comment optimisé mes tables, j'ai activé le log-queries-not-using-indexes
Dans la log, j'ai ce type de requête qui est ressortie, quel type d'index placeriez vous ??

Code:

1
2
3
4
5
6
7
# Query_time: 1  Lock_time: 0  Rows_sent: 1005  Rows_examined: 548139
SELECT t_game2.ID AS ID, IDaraj,youth, IDclub, IDclub2, adate, atime,daraj_test, araj_test,cup,COALESCE(t_araj_test.sort,50000) AS gmSort,status,cl1.club AS club1, cl1.dclub AS dclub1, cl2.dclub AS dclub2,cl2.club AS club2, t_araj_test.IDcountry AS IDcountry, country,t_country.sms AS countryCode, result, result2, resultH, resultH2, resultF, resultF2, resultE, resultE2, resultP, resultP2, isLast 
FROM t_game2 LEFT JOIN t_araj_test ON t_araj_test.ID=IDaraj 
LEFT JOIN t_country ON t_country.ID=t_araj_test.IDcountry 
LEFT JOIN t_club as cl1 ON cl1.ID=IDclub  
LEFT JOIN t_club as cl2 ON cl2.ID=IDclub2 
WHERE ( (adate > '2019-10-05 11:00:00' and adate < '2019-10-06 15:00:00') ) AND cl1.display=1 AND cl2.display=1  ORDER BY COALESCE(t_araj_test.sort,50000), araj_test, adate, atime;

Merci beaucoup pour votre aide

Bonjour,

On ne peut pas vous aider sur un problème de performances sans la description exacte des tables (de préférence, communiquez le DDL complet des ordres CREATE TABLE et CREATE INDEX)

Ce qu'il faut vérifier en premier, c'est la présence d'index sur des colonnes de même type et même longueur pour les critères de filtrage et de jointure

Quelques remarques en passant :

vos jointures OUTER sont implicitement remplacées par des jointures INNER à cause des restrictions AND cl1.display=1 AND cl2.display=1
supprimez les parenthèses dans la clause WHERE, il n'y a que des AND, elles sont donc in
vous devriez utiliser des alias pour chaque table et préfixer toutes les colonnes par ces alias, la compréhension de la requête en sera facilitée (ici on ne sait pas quelle colonne vient de quelle table)

Citation:

Envoyé par omelhor

Bonjour,

J'ai de gros soucis de lenteur sur ma bdd, du coup je recherche comment optimisé mes tables, j'ai activé le log-queries-not-using-indexes
Dans la log, j'ai ce type de requête qui est ressortie, quel type d'index placeriez vous ??

Que dit le EXPLAIN de cette requête? Et de quoi ont l'air les tables impliquées?

1 pièce(s) jointe(s)

Bonjour,

Je m'excuse pour la réponse tardive, voici la structure de tables, et le résultat du EXPLAIN

Code:

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
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
-- --------------------------------------------------------
 
--
-- Structure de la table `t_game2`
--
 
CREATE TABLE IF NOT EXISTS `t_game2` (
  `ID` int(7) NOT NULL,
  `IDclub` int(8) NOT NULL default '0',
  `IDclub2` int(8) NOT NULL default '0',
  `adate` date NOT NULL default '0000-00-00',
  `IDreferee` smallint(2) default NULL,
  `IDreferee2` smallint(2) unsigned NOT NULL default '0',
  `IsLast` smallint(1) default NULL,
  `mainTime` int(11) NOT NULL default '0',
  `addedTime` float(10,2) NOT NULL default '0.00',
  `game` varchar(10) NOT NULL default '',
  `IDaraj` int(11) NOT NULL default '0',
  `IDstadium` varchar(50) default NULL,
  `IDlevel` tinyint(3) unsigned NOT NULL default '0',
  `IDgrup` tinyint(3) unsigned NOT NULL default '0',
  `atime` time default NULL,
  `resultH` tinyint(3) unsigned NOT NULL default '0',
  `resultH2` tinyint(3) unsigned NOT NULL default '0',
  `result` tinyint(3) unsigned NOT NULL default '0',
  `result2` tinyint(3) unsigned NOT NULL default '0',
  `resultF` tinyint(3) unsigned NOT NULL default '0',
  `resultF2` tinyint(3) unsigned NOT NULL default '0',
  `resultE` tinyint(3) unsigned NOT NULL default '0',
  `resultE2` tinyint(3) unsigned NOT NULL default '0',
  `resultP` tinyint(3) unsigned NOT NULL default '0',
  `resultP2` tinyint(3) unsigned NOT NULL default '0',
  `status` tinyint(3) unsigned NOT NULL default '0',
  `home` float(10,2) default NULL,
  `draw` float(10,2) default NULL,
  `away` float(10,2) default NULL,
  `homeLink` text,
  `coach1` varchar(100) default NULL,
  `coach2` varchar(100) default NULL,
  `statistic` tinyint(4) NOT NULL default '0',
  `home_ch` float(10,3) default NULL,
  `draw_ch` float(10,3) default NULL,
  `away_ch` float(10,3) default NULL,
  `homeLink_ch` text,
  `referee` varchar(100) default NULL,
  `stadium` varchar(100) character set utf8 collate utf8_unicode_ci default NULL,
  `penal` tinyint(3) unsigned default '0',
  `att` int(11) NOT NULL default '0',
  `frms` varchar(10) NOT NULL default '',
  `frms2` varchar(10) NOT NULL default '',
  `season` varchar(15) default NULL,
  `agrRes` varchar(50) NOT NULL default '',
  `agrWin` tinyint(4) NOT NULL default '0',
  `startTm` int(11) NOT NULL default '0',
  `day` smallint(3) default NULL,
  `ts` timestamp NOT NULL default CURRENT_TIMESTAMP,
  `weather1` varchar(100) NOT NULL default '',
  `weather2` varchar(100) NOT NULL default '0',
  `weather3` int(11) NOT NULL default '0',
  `disabled` tinyint(3) unsigned NOT NULL default '0',
  PRIMARY KEY  (`ID`),
  UNIQUE KEY `ccda` (`IDclub`,`IDclub2`,`adate`,`IDaraj`),
  KEY `IDclub` (`IDclub`),
  KEY `IDclub2` (`IDclub2`),
  KEY `idx_adate` (`adate`),
  KEY `idx_IDaraj` (`IDaraj`),
  KEY `idx_IDclub2_adate` (`IDclub2`,`adate`),
  KEY `idx_IDclub_adate` (`adate`,`IDclub`),
  KEY `idx_status` (`status`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;
 
 
-- --------------------------------------------------------
 
--
-- Structure de la table `t_araj_test`
--
 
CREATE TABLE IF NOT EXISTS `t_araj_test` (
  `ID` int(11) NOT NULL,
  `araj_test` varchar(100) NOT NULL default '',
  `araj_test_country` varchar(100) NOT NULL,
  `daraj_test` varchar(100) default NULL,
  `daraj_test_old` varchar(100) NOT NULL,
  `id2` smallint(6) NOT NULL,
  `stand_actif` int(11) NOT NULL default '0',
  `display` tinyint(2) NOT NULL,
  `cup` tinyint(3) NOT NULL default '0',
  `champ` int(11) NOT NULL,
  `sort` smallint(6) NOT NULL default '0',
  `date_last_match` date NOT NULL,
  `feed` tinyint(2) NOT NULL,
  `sort_fr` smallint(6) NOT NULL default '0',
  `IDcountry` int(11) NOT NULL default '0',
  `first` tinyint(4) NOT NULL default '0',
  `youth` tinyint(4) NOT NULL default '0',
  `IDsport` tinyint(4) NOT NULL default '1',
  `araj_test_ch` varchar(100) character set utf8 collate utf8_unicode_ci NOT NULL default '',
  `araj_test_ch_country` varchar(100) default NULL,
  `araj_seo` varchar(100) NOT NULL,
  `araj_seo_old` varchar(100) NOT NULL,
  `araj_seo_comp` varchar(100) NOT NULL,
  `url` varchar(255) NOT NULL,
  `youtube` varchar(255) NOT NULL,
  `twitter` varchar(255) NOT NULL,
  `facebook` varchar(255) NOT NULL,
  `google` varchar(255) NOT NULL,
  `instagram` varchar(255) NOT NULL,
  `wap` varchar(50) NOT NULL default '',
  `showCountry` tinyint(2) NOT NULL default '0',
  `ts` timestamp NOT NULL default CURRENT_TIMESTAMP,
  `IDstage` int(11) unsigned default NULL,
  `disabled` tinyint(3) NOT NULL default '0',
  `priority` tinyint(3) NOT NULL default '0',
  `teams` tinyint(3) NOT NULL default '0',
  `gmBetween` tinyint(3) NOT NULL default '0',
  `IDcompetition` int(11) NOT NULL default '0',
  `commentaire` text NOT NULL,
  `hasGames` tinyint(1) NOT NULL,
  PRIMARY KEY  (`ID`),
  UNIQUE KEY `araj_test` (`araj_test`),
  UNIQUE KEY `idx_daraj_test` (`daraj_test`),
  KEY `idx_sort` (`sort`),
  KEY `idx_IDcountry` (`IDcountry`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;
 
-- --------------------------------------------------------
 
--
-- Structure de la table `t_club`
--
 
CREATE TABLE IF NOT EXISTS `t_club` (
  `ID` int(11) NOT NULL,
  `club` varchar(50) character set utf8 collate utf8_unicode_ci NOT NULL default '',
  `IDcountry` int(11) NOT NULL default '0',
  `coach` varchar(50) default NULL,
  `dclub` varchar(50) default NULL,
  `hasGames` tinyint(1) NOT NULL,
  `display` tinyint(4) NOT NULL default '0',
  `IDbdfutbol` int(11) NOT NULL,
  `IDxmlsoccer` int(11) NOT NULL,
  `dateMaj` date default NULL,
  PRIMARY KEY  (`ID`),
  UNIQUE KEY `idx_dclub` (`dclub`),
  KEY `idx_display` (`display`),
  KEY `idx_club` (`club`),
  KEY `idx_ID_display` (`ID`,`display`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;
 
-- --------------------------------------------------------
 
--
-- Structure de la table `t_country`
--
 
CREATE TABLE IF NOT EXISTS `t_country` (
  `ID` int(11) NOT NULL,
  `IDcontinent` tinyint(3) NOT NULL default '0',
  `country` varchar(50) NOT NULL default '',
  `Demonym` varchar(100) NOT NULL,
  `wiki_en` varchar(255) NOT NULL,
  `display` int(1) NOT NULL,
  `country_seo` varchar(100) NOT NULL,
  `country_ru` varchar(50) NOT NULL default '',
  `country_ch` varchar(50) NOT NULL default '',
  `country_tr` varchar(50) default NULL,
  `country_hb` varchar(50) default NULL,
  `country_nl` varchar(50) default NULL,
  `sms` char(2) default NULL,
  `country_de` varchar(50) default NULL,
  `country_fr` varchar(100) NOT NULL default '',
  `country_fr_seo` varchar(100) NOT NULL,
  `habitant_fr` varchar(100) NOT NULL,
  `wiki_fr` varchar(255) NOT NULL,
  `country_am` varchar(250) NOT NULL default '',
  `country_r2` varchar(200) NOT NULL default '',
  `bgcolor` varchar(10) NOT NULL default '',
  `fgcolor` varchar(10) NOT NULL default '',
  `flag` varchar(50) NOT NULL default '',
  `flag_fr` varchar(50) NOT NULL,
  PRIMARY KEY  (`ID`),
  UNIQUE KEY `country_idx` (`country`),
  UNIQUE KEY `country_fr_idx` (`country_fr`),
  UNIQUE KEY `country_seo_idx` (`country_seo`),
  KEY `idx_country_fr_seo` (`country_fr_seo`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

Résultat du EXPLAIN (photo )

Pièce jointe 508478

Merci

Il est normal que vous ayez des problèmes de performances. Votre table est TRES mal modélisée. Aucun des principes de modélisation (formes normales notamment) n'ont été appliqués.
Dès lors mettre des index constituera un puits sans fond à objectif perdu….
Commencez par normaliser votre table et vous aurez peut être même pas besoin de l'indexer….

Lisez l'article que j'ai écrit à ce sujet :
https://blog.developpez.com/sqlpro/p...mances_petites

A +

Dur à encaisser, du coup je me rends compte que je dois tout revoir, mais merci beaucoup !!!

Effectivement, les tables sont obèses et c'est symptomatique d'une modélisation bâclée.
N'hésitez pas à poser des questions dans le forum consacré à la modélisation qui se trouve ici :
https://www.developpez.net/forums/f6...sation/schema/

Bonjour,

vous pourriez toutefois créer un index sur la colonne adate de la table t_game2, en ajoutant éventuellement les colonnes de cette table utilisées dans la requête afin de rendre l'index couvrant (et donc minimiser l'impact de la mauvaise modélisation)

Autrement dit remodéliser à l'envers en faisant enfler la volumétrie globale….

A +