teratail
回答率
85.35%
teratail header banner
teratail header banner
質問するログイン新規登録
トップに関する質問ブランクが多いカラムをJOINする際のインデックスの効かせ方

編集履歴

質問編集履歴

1

再現に必要なテーブルを作成するためのSQLを追加しました

2020/10/09 09:28

投稿

Rio_1201
Rio_1201

スコア2

test CHANGED
File without changes
test CHANGED
@@ -79,3 +79,205 @@
79
79
  可能であればuser_dataの値を変えたりすることなくインデックスを効かせ
80
80
 
81
81
  レスポンスを改善させたいのですが、何か方法はございますでしょうか。
82
+
83
+
84
+
85
+ 不要な情報を全て削ったabc_data,user_dataを作成するSQLは下記になります。
86
+
87
+ こちらで上述のSQLを実行した際にuser_dataのabc_codeのインデックスを使用し検索が実行したい、という要望になります。
88
+
89
+ ```Mysql
90
+
91
+ CREATE TABLE `abc_data` (
92
+
93
+ `rowid` int(11) NOT NULL AUTO_INCREMENT,
94
+
95
+ `abc_code` varchar(10) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NOT NULL DEFAULT '',
96
+
97
+ PRIMARY KEY (`rowid`) USING BTREE,
98
+
99
+ INDEX `abc_code`(`abc_code`) USING BTREE
100
+
101
+ ) ENGINE = MyISAM AUTO_INCREMENT = 7820 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_bin ROW_FORMAT = Dynamic;
102
+
103
+
104
+
105
+ INSERT INTO `abc_data` VALUES (1, 'ABCD');
106
+
107
+ INSERT INTO `abc_data` VALUES (2, 'EFGH');
108
+
109
+ INSERT INTO `abc_data` VALUES (3, 'SETH');
110
+
111
+ INSERT INTO `abc_data` VALUES (4, 'EYUK');
112
+
113
+ INSERT INTO `abc_data` VALUES (5, 'ROPN');
114
+
115
+ INSERT INTO `abc_data` VALUES (6, 'SEKB');
116
+
117
+ INSERT INTO `abc_data` VALUES (7, 'HORP');
118
+
119
+ INSERT INTO `abc_data` VALUES (8, 'XKEM');
120
+
121
+ INSERT INTO `abc_data` VALUES (9, 'EOIF');
122
+
123
+ INSERT INTO `abc_data` VALUES (10, 'EMSO');
124
+
125
+ ```
126
+
127
+ ```Mysql
128
+
129
+ CREATE TABLE `user_data` (
130
+
131
+ `userid` int(11) NOT NULL AUTO_INCREMENT,
132
+
133
+ `abc_code` varchar(10) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL DEFAULT '',
134
+
135
+ PRIMARY KEY (`userid`) USING BTREE,
136
+
137
+ INDEX `abc_code`(`abc_code`) USING BTREE
138
+
139
+ ) ENGINE = InnoDB AUTO_INCREMENT = 100400622 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci ROW_FORMAT = Dynamic;
140
+
141
+
142
+
143
+ INSERT INTO `user_data` VALUES (1, '');
144
+
145
+ INSERT INTO `user_data` VALUES (2, '');
146
+
147
+ INSERT INTO `user_data` VALUES (3, '');
148
+
149
+ INSERT INTO `user_data` VALUES (4, '');
150
+
151
+ INSERT INTO `user_data` VALUES (5, '');
152
+
153
+ INSERT INTO `user_data` VALUES (6, '');
154
+
155
+ INSERT INTO `user_data` VALUES (7, '');
156
+
157
+ INSERT INTO `user_data` VALUES (8, '');
158
+
159
+ INSERT INTO `user_data` VALUES (9, '');
160
+
161
+ INSERT INTO `user_data` VALUES (10, '');
162
+
163
+ INSERT INTO `user_data` VALUES (11, '');
164
+
165
+ INSERT INTO `user_data` VALUES (12, '');
166
+
167
+ INSERT INTO `user_data` VALUES (13, '');
168
+
169
+ INSERT INTO `user_data` VALUES (14, '');
170
+
171
+ INSERT INTO `user_data` VALUES (15, '');
172
+
173
+ INSERT INTO `user_data` VALUES (16, '');
174
+
175
+ INSERT INTO `user_data` VALUES (17, '');
176
+
177
+ INSERT INTO `user_data` VALUES (18, '');
178
+
179
+ INSERT INTO `user_data` VALUES (19, '');
180
+
181
+ INSERT INTO `user_data` VALUES (20, '');
182
+
183
+ INSERT INTO `user_data` VALUES (21, '');
184
+
185
+ INSERT INTO `user_data` VALUES (22, '');
186
+
187
+ INSERT INTO `user_data` VALUES (23, '');
188
+
189
+ INSERT INTO `user_data` VALUES (24, '');
190
+
191
+ INSERT INTO `user_data` VALUES (25, '');
192
+
193
+ INSERT INTO `user_data` VALUES (26, '');
194
+
195
+ INSERT INTO `user_data` VALUES (27, '');
196
+
197
+ INSERT INTO `user_data` VALUES (28, '');
198
+
199
+ INSERT INTO `user_data` VALUES (29, '');
200
+
201
+ INSERT INTO `user_data` VALUES (30, '');
202
+
203
+ INSERT INTO `user_data` VALUES (31, '');
204
+
205
+ INSERT INTO `user_data` VALUES (32, '');
206
+
207
+ INSERT INTO `user_data` VALUES (33, '');
208
+
209
+ INSERT INTO `user_data` VALUES (34, '');
210
+
211
+ INSERT INTO `user_data` VALUES (35, '');
212
+
213
+ INSERT INTO `user_data` VALUES (36, '');
214
+
215
+ INSERT INTO `user_data` VALUES (37, '');
216
+
217
+ INSERT INTO `user_data` VALUES (38, '');
218
+
219
+ INSERT INTO `user_data` VALUES (39, '');
220
+
221
+ INSERT INTO `user_data` VALUES (40, '');
222
+
223
+ INSERT INTO `user_data` VALUES (41, '');
224
+
225
+ INSERT INTO `user_data` VALUES (42, '');
226
+
227
+ INSERT INTO `user_data` VALUES (43, '');
228
+
229
+ INSERT INTO `user_data` VALUES (44, '');
230
+
231
+ INSERT INTO `user_data` VALUES (45, '');
232
+
233
+ INSERT INTO `user_data` VALUES (46, '');
234
+
235
+ INSERT INTO `user_data` VALUES (47, '');
236
+
237
+ INSERT INTO `user_data` VALUES (48, '');
238
+
239
+ INSERT INTO `user_data` VALUES (49, '');
240
+
241
+ INSERT INTO `user_data` VALUES (50, '');
242
+
243
+ INSERT INTO `user_data` VALUES (51, '');
244
+
245
+ INSERT INTO `user_data` VALUES (52, '');
246
+
247
+ INSERT INTO `user_data` VALUES (53, '');
248
+
249
+ INSERT INTO `user_data` VALUES (54, '');
250
+
251
+ INSERT INTO `user_data` VALUES (55, '');
252
+
253
+ INSERT INTO `user_data` VALUES (56, '');
254
+
255
+ INSERT INTO `user_data` VALUES (57, '');
256
+
257
+ INSERT INTO `user_data` VALUES (58, '');
258
+
259
+ INSERT INTO `user_data` VALUES (59, '');
260
+
261
+ INSERT INTO `user_data` VALUES (60, '');
262
+
263
+ INSERT INTO `user_data` VALUES (61, '');
264
+
265
+ INSERT INTO `user_data` VALUES (62, '');
266
+
267
+ INSERT INTO `user_data` VALUES (63, '');
268
+
269
+ INSERT INTO `user_data` VALUES (64, '');
270
+
271
+ INSERT INTO `user_data` VALUES (65, '');
272
+
273
+ INSERT INTO `user_data` VALUES (66, '');
274
+
275
+ INSERT INTO `user_data` VALUES (67, 'DKEO');
276
+
277
+ INSERT INTO `user_data` VALUES (70, 'DKEO');
278
+
279
+ INSERT INTO `user_data` VALUES (68, 'EMSK');
280
+
281
+ INSERT INTO `user_data` VALUES (69, 'LOES');
282
+
283
+ ```