Créer un index sur plusieurs champs ou pas ?

**dbejean** · 26/03/2008, 11h25

Bonjour,

Voici une requête sur 3 tables et le résultat du EXPLAIN pour illustrer ma question. Cette requête s'execute en 30 secondes !

explain select i.id, i.title, i.description, i.pubdate, i.item_url, i.item_url_domain, c.link
from rss_item as i join rss_channel as c on i.id_channel = c.id join rss_favoritchannel as fc use index (index_02 )on c.id = fc.id_channel
where i.language_root = 'fr' and i.pubdate <= now('') and to_days(now()) - to_days(i.pubdate) <= 3 and fc.id_user = 20 order by i.pubdate desc limit 0,5

| 1 | SIMPLE | i | ref | index_01,index_03,index_05 | index_05 | 6 | const | 287526 | Using where; Using filesort |
| 1 | SIMPLE | c | eq_ref | PRIMARY | PRIMARY | 4 | crossfeeds.i.id_channel | 1 | |
| 1 | SIMPLE | fc | ref | index_02 | index_02 | 4 | crossfeeds.c.id | 1 | Using where |

On voit que cette requête est susceptible d'utiliser 3 index pour la tables "i" (rss_item) : sur i.id_channel (index_01), sur i.pubdate (index_03) et sur i.language_root (index_05).

Pour cette requête, faut-il créer un index sur les 3 champs ?

INDEX index_09 (id_channel, pubdate, language_root )

Si oui :

l'ordre de déclaration des champs dans l'index est-il important ?

faut-il supprimer les index "unitaires" ou les conserver pour d'autres requêtes ne portant pas sur les 3 champs simultanément ?

Merci d'avance.

**Chengj** · 26/03/2008, 14h19

Pourrais-tu aussi donner la structure de tes tables ? Ce n'est pas simple de deviner comme ça

.

**Maximil ian** · 26/03/2008, 14h27

Envoyé par dbejean

On voit que cette requête est susceptible d'utiliser 3 index pour la tables "i" (rss_item) : sur i.id_channel (index_01), sur i.pubdate (index_03) et sur i.language_root (index_05).

Pour cette requête, faut-il créer un index sur les 3 champs ?

Il faudrait tester mais je verrais plutôt un index sur (language_root, pubdate). Bizarre que index_01 soit un des index considérés par l'optimiseur.

Envoyé par dbejean

faut-il supprimer les index "unitaires" ou les conserver pour d'autres requêtes ne portant pas sur les 3 champs simultanément ?

Tu peux les supprimer si toutes tes autres requêtes portent seulement sur des parties gauches de l'index. Ex : un index sur (col1, col2) sera utilisé dans les requêtes mettant en jeu dans leur clause WHERE col1 et col2, col1 seul mais pas col2 seul.
En général garder des index individuels est quand même utile. Après il faut voir en termes d'espace disque occupé par l'index et de performances en écriture (plus il y a d'index, moins c'est performant).

Tu peux essayer de déconstruire ta requête (enlever les jointures, etc.) et de la reconstruire petit à petit pour voir ce qui la rend lente.
Moi je remplacerais le

Code :

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

and i.pubdate <= now('') and to_days(now()) - to_days(i.pubdate) <= 3

par

Code :

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

AND i.pubdate <=  DATE_SUB (NOW(), INTERVAL 3 DAY)

voire

Code :

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

AND i.pubdate <=  [chaine date du jour -3 calculée par le code client]

**dbejean** · 26/03/2008, 14h43

Merci pour ces réponses.

@Chengj
Voici la structure des tables.

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
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
188
189
190
191
192
193
194
195
196
197
198
199
200
 
#
# Table structure for table 'rss_channel'
#
DROP TABLE IF EXISTS rss_channel;
CREATE TABLE rss_channel (
  id int(11) unsigned auto_increment,
  createtime datetime NOT NULL, 
  read_lasttime datetime,
  read_nexttime datetime,
  used_lasttime datetime,
  content TEXT,
  status_read tinyint(2) unsigned DEFAULT 0,
  status_index tinyint(1) unsigned DEFAULT 0,
  status_message varchar(255), 
  nbitems_read int(2) unsigned DEFAULT 0,  
 
  id_father int(11) unsigned DEFAULT 0,
 
  type char(16),
 
  popularity int(11) unsigned DEFAULT 0,  
  refreshperiod int(11) unsigned DEFAULT 0,  
  content_length int(11) unsigned DEFAULT 0,  
 
  status_error_count int(3) unsigned DEFAULT 0,  
  status_error_firstdate datetime,  
 
  condget_lastmodified  char(100),
  condget_etag  char(255),
  channel_url_checksum_crc char(16) NOT NULL DEFAULT '0',
 
  channel_url varchar(255) NOT NULL,
  channel_url_lowercase varchar(255) NOT NULL,  
    # rss  : 
    # opml : /outline["xmlUrl"]
 
  channel_url_domain varchar(64) NOT NULL DEFAULT 'unknown',  
  channel_url_masterdomain char(16) NOT NULL DEFAULT 'unknown',    
 
  title TEXT,
    # rss  : /channel/title 
    # opml : /outline["title"]
 
  description TEXT,
    # rss  : /channel/description
    # opml : /outline["description"]
 
  link varchar(255),
    # Site d'origine
    # rss  : /channel/link
    # opml : /outline["htmlurl"]
 
  language varchar(32) NOT NULL DEFAULT 'unknown',  
  language_root char(2) NOT NULL DEFAULT 'xx',  
    # optionnel
    # rss  : /channel/language 
    # opml : /outline["language"]
 
  language_root_inchannel char(2) NOT NULL DEFAULT 'xx',  
 
  original_encoding varchar(32) NOT NULL DEFAULT 'unknown',
 
  country varchar(128) NOT NULL DEFAULT 'unknown',  
 
  lastbuilddate datetime,  
    # optionnel
    # rss : /channel/lastBuildDate 
 
  pubdate datetime,  
    # optionnel
    # rss : /channel/pubdate 
 
  category varchar(1023),
    # optionnel
    # rss  : /channel/category
 
  author varchar(255),
 
  image_title varchar(255),
  image_link varchar(255),
  image_url varchar(255),
  image_description varchar(255),
 
  copyright varchar(255),
  copyrighted int(2) unsigned DEFAULT 0,   
 
  geo_ip  varchar(16),
  geo_country_name  varchar(127),
  geo_country_code  varchar(32),
  geo_area  varchar(127),
  geo_city  varchar(127),
  geo_latitude varchar(32),
  geo_longitude varchar(32),
 
  PRIMARY KEY (id),
  UNIQUE KEY url (channel_url_lowercase),
  INDEX index_01 (title (20)),
  INDEX index_02 (status_read),
  INDEX index_03 (status_index),
  INDEX index_04 (read_nexttime),
  INDEX index_05 (read_lasttime),  
  INDEX index_06 (channel_url_masterdomain),
  INDEX index_07 (language_root),
  INDEX index_08 (status_error_count),
  INDEX index_09 (nbitems_read),
  INDEX index_10 (createtime),  
  INDEX index_11 (pubdate),
  INDEX index_12 (used_lasttime),
  INDEX index_13 (popularity),   
  INDEX index_14 (id_father)   
)
ENGINE MyISAM
DELAY_KEY_WRITE=1
MAX_ROWS=1000000 MIN_ROWS=10000
CHARACTER SET utf8 COLLATE utf8_general_ci;
;
 
 
#
# Table structure for table 'rss_item'
#
DROP TABLE IF EXISTS rss_item;
CREATE TABLE rss_item (
  id int(11) unsigned auto_increment,
  id_channel int(11) unsigned NOT NULL,   
  createtime datetime NOT NULL, 
  status_index tinyint(1) unsigned DEFAULT 0,
  pubdate datetime,  
 
  item_url varchar(2047) NOT NULL,
  item_url_lowercase varchar(333) NOT NULL,  
  item_url_domain varchar(64) NOT NULL,  
  item_url_masterdomain char(16) NOT NULL,  
 
  title TEXT,
  description TEXT,
 
  crcitem varchar(64),
 
  category varchar(1023),
  author varchar(1023),
 
  language varchar(32) NOT NULL DEFAULT 'unknown',  
  language_root char(2) NOT NULL DEFAULT 'xx',  
 
  PRIMARY KEY (id),
  UNIQUE KEY key_01 (item_url (128)),
  #UNIQUE KEY key_02 (item_url_domain, title (20), pubdate),
  UNIQUE KEY key_02 (item_url_domain, title (20), crcitem),
  INDEX index_01 (id_channel), 
  INDEX index_02 (status_index), 
  INDEX index_03 (pubdate), 
  INDEX index_04 (item_url_masterdomain),
  INDEX index_05 (language_root),
  INDEX index_06 (createtime),
  INDEX index_07 (title (20))
 
  #alter table rss_item add index_08 (id_channel, pubdate)
)
ENGINE MyISAM
DELAY_KEY_WRITE=1
MAX_ROWS=500000000 MIN_ROWS=10000
CHARACTER SET utf8 COLLATE utf8_general_ci;
;  
 
#
# Table structure for table 'rss_favoritchannel'
#
DROP TABLE IF EXISTS rss_favoritchannel;
CREATE TABLE rss_favoritchannel (
  id int(11) unsigned auto_increment,
  id_user int(11)  unsigned NOT NULL,  
  id_channel int(11)  unsigned NOT NULL,  
  createtime datetime,
 
  tags varchar(255) NOT NULL DEFAULT 'none',    
  description TEXT,
 
  classification_path varchar(255),    
  classification_level1 varchar(63), 
  classification_level2 varchar(63), 
  classification_level3 varchar(63), 
  classification_level4 varchar(63), 
 
  PRIMARY KEY (id),
  UNIQUE KEY ids (id_user, id_channel),        
  INDEX index_01 (id_user), 
  INDEX index_02 (id_channel),
  INDEX index_03 (classification_path),
  INDEX index_04 (classification_level1),
  INDEX index_05 (classification_level2),
  INDEX index_06 (classification_level3),
  INDEX index_07 (classification_level4)
)
ENGINE MyISAM
DELAY_KEY_WRITE=1
MAX_ROWS=1000000 MIN_ROWS=10000
CHARACTER SET utf8 COLLATE utf8_general_ci;
;

**Maximil ian** · 26/03/2008, 15h24

je rectifie : il faudrait remplacer par

Code :

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

AND i.pubdate BETWEEN DATE_SUB (NOW(), INTERVAL 3 DAY) AND NOW()

PS : combien de lignes contient la table rss_item ?

**dbejean** · 26/03/2008, 16h02

@Maximilian

La table rss_item contient 1 millions d'éléments mais pourra monter jusqu'à 10 ou 100 millions.

La table rss_channel contient 600.000 elements mais pourra monter jusqu'à 1 ou 2 millions.

Aujourd'hui, seuls 6000 channels sont associés à des items soit en moyenne 150. Mais c'est variable, ça va de 10 à plus de 1000.

**Maximil ian** · 26/03/2008, 16h09

L'utilisation de l'index index_05 amène MySQL à considérer 287526 lignes comme on le voit dans l'explain. Peut-être qu'une utilisation de l'index sur fc.id_user serait plus efficace. Pour cela il faudrait enlever le "use index (index_02)" (à vérifier).

Par ailleurs il serait bon de donner un nom unique à chacun de tes index car là c'est un peu le fouillis

**dbejean** · 26/03/2008, 16h51

J'ai fait pas mal de tests et ce qui fonctionne bien est ceci :

Code :

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

1
2
3
explain select i.id, i.title, i.description, i.pubdate, i.item_url, i.item_url_domain, c.link
from rss_item as i use index (index_03) join rss_channel as c on i.id_channel = c.id join rss_favoritchannel as fc on c.id = fc.id_channel
where i.language_root = 'fr' and i.pubdate <= now('') and to_days(now()) - to_days(i.pubdate) <= 3 and fc.id_user = 20 order by i.pubdate desc limit 0,5

J'ai supprimé le use index (index_02) et ajouté pour la table rss_item un use index (index_03) pour i.pubdate.

Mes noms d'index sont uniques mais effectivement pas très parlant

Merci pour la restriction sur la date qui est beaucoup plus lisible et la reponse pour les index composites.

**Maximil ian** · 26/03/2008, 17h17

Et tu constates un gros gain de performance ?

**dbejean** · 26/03/2008, 18h02

Gain énorme je passe de 25 sec à 0,5 sec.

Par contre, si j'ajoute une restriction and fc.tags = 'xxx', c'est le contraire je repasse à 15 secondes et il faut que je retire le index use pour avoir un temps < 1 sec.

Je comprend que la restriction permette de bonnes perf sans le index use car il y a moins de lignes dans le résultat, mais je ne vois pas pourquoi le index use est si pénalisant dans ce cas.

Mon code PHP gère ça et utilise le index use uniquement si il n'y a pas de restriction sur fc.tags

Créer un index sur plusieurs champs ou pas ?

Requêtes MySQL

Discussions similaires

Partager

Partager