質問編集履歴
3
式インデックスの検証を行ったので追記
test
CHANGED
File without changes
|
test
CHANGED
@@ -10,6 +10,14 @@
|
|
10
10
|
|
11
11
|
|
12
12
|
|
13
|
+
・追記
|
14
|
+
|
15
|
+
asinテーブルのwhereに使用する「FlagSer&20」を式インデックスとして作成しましたが、結果は変わりませんでした。
|
16
|
+
|
17
|
+
> KEY `index_test2` (`MonorateBuyNum`,`SettingProductItem`,((`FlagSer` & 20)),`ASIN`)
|
18
|
+
|
19
|
+
|
20
|
+
|
13
21
|
### クエリとexplain
|
14
22
|
|
15
23
|
```ここに言語を入力
|
@@ -346,7 +354,11 @@
|
|
346
354
|
|
347
355
|
KEY `dhash_idx` (`DHashSetTime`,`FlagSer`),
|
348
356
|
|
349
|
-
KEY `ureteru_get_index` (`MonorateBuyNum`,`SettingProductItem`,`FlagSer`,`ASIN`)
|
357
|
+
KEY `ureteru_get_index` (`MonorateBuyNum`,`SettingProductItem`,`FlagSer`,`ASIN`),
|
358
|
+
|
359
|
+
KEY `index_test` (((`FlagSer` & 20))),
|
360
|
+
|
361
|
+
KEY `index_test2` (`MonorateBuyNum`,`SettingProductItem`,((`FlagSer` & 20)),`ASIN`)
|
350
362
|
|
351
363
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci |------+
|
352
364
|
|
2
記載するcreate tableを間違えた
test
CHANGED
File without changes
|
test
CHANGED
@@ -180,32 +180,200 @@
|
|
180
180
|
|
181
181
|
```ここに言語を入力
|
182
182
|
|
183
|
-
|
184
|
-
|
185
|
-
|
|
186
|
-
|
187
|
-
`S
|
188
|
-
|
189
|
-
`
|
190
|
-
|
191
|
-
`
|
192
|
-
|
193
|
-
`
|
194
|
-
|
195
|
-
`
|
196
|
-
|
197
|
-
|
198
|
-
|
199
|
-
|
200
|
-
|
201
|
-
|
202
|
-
|
203
|
-
|
204
|
-
|
205
|
-
|
183
|
+
//asinテーブル
|
184
|
+
|
185
|
+
| asin | CREATE TABLE `asin` (
|
186
|
+
|
187
|
+
`ASIN` varchar(10) COLLATE utf8mb4_general_ci NOT NULL,
|
188
|
+
|
189
|
+
`JAN` varchar(13) COLLATE utf8mb4_general_ci NOT NULL,
|
190
|
+
|
191
|
+
`Title` varchar(1024) COLLATE utf8mb4_general_ci DEFAULT NULL,
|
192
|
+
|
193
|
+
`ImageUrl` varchar(2048) COLLATE utf8mb4_general_ci DEFAULT NULL,
|
194
|
+
|
195
|
+
`FixFee` smallint(6) DEFAULT NULL,
|
196
|
+
|
197
|
+
`FeeRate` float(2,2) DEFAULT NULL,
|
198
|
+
|
199
|
+
`PriceFBA` int(11) DEFAULT NULL,
|
200
|
+
|
201
|
+
`PriceSelf` int(11) DEFAULT NULL,
|
202
|
+
|
203
|
+
`PriceCart` int(11) DEFAULT NULL,
|
204
|
+
|
205
|
+
`NotExit` bit(1) NOT NULL,
|
206
|
+
|
207
|
+
`SettingFee` bit(2) NOT NULL,
|
208
|
+
|
209
|
+
`SettingProductItem` bit(2) NOT NULL,
|
210
|
+
|
211
|
+
`SettingJAN` bit(2) NOT NULL,
|
212
|
+
|
213
|
+
`PriceSetTime` bigint(20) NOT NULL,
|
214
|
+
|
215
|
+
`ProductItemSetTime` bigint(20) NOT NULL,
|
216
|
+
|
217
|
+
`FeeSetTime` bigint(20) NOT NULL,
|
218
|
+
|
219
|
+
`SellerNum` smallint(6) DEFAULT NULL,
|
220
|
+
|
221
|
+
`HadFBA` bit(1) NOT NULL,
|
222
|
+
|
223
|
+
`SettingMonorate` bit(2) NOT NULL,
|
224
|
+
|
225
|
+
`MonorateSetTime` bigint(20) NOT NULL,
|
226
|
+
|
227
|
+
`MonorateBuyNum` smallint(6) NOT NULL,
|
228
|
+
|
229
|
+
`TitleOpt` varchar(1024) COLLATE utf8mb4_general_ci DEFAULT NULL,
|
230
|
+
|
231
|
+
`Danger` bit(1) NOT NULL,
|
232
|
+
|
233
|
+
`Claim` bit(1) NOT NULL,
|
234
|
+
|
235
|
+
`MonorateNoExitRate` float(3,3) NOT NULL,
|
236
|
+
|
237
|
+
`MonorateNoExitRateTotal` float(3,3) NOT NULL,
|
238
|
+
|
239
|
+
`SettingCategory` bit(2) NOT NULL,
|
240
|
+
|
241
|
+
`CategorySetTime` bigint(20) NOT NULL,
|
242
|
+
|
243
|
+
`CategoryHash` int(10) unsigned DEFAULT NULL,
|
244
|
+
|
245
|
+
`CategoryHash2` int(10) unsigned DEFAULT NULL,
|
246
|
+
|
247
|
+
`CategoryHash3` int(10) unsigned DEFAULT NULL,
|
248
|
+
|
249
|
+
`PriceUsedFBA` int(11) DEFAULT NULL,
|
250
|
+
|
251
|
+
`PriceUsedSelf` int(11) DEFAULT NULL,
|
252
|
+
|
253
|
+
`DHashSetTime` bigint(20) NOT NULL,
|
254
|
+
|
255
|
+
`DHash` varchar(1024) COLLATE utf8mb4_general_ci DEFAULT NULL,
|
256
|
+
|
257
|
+
`RankingCategoryHash1` int(10) unsigned NOT NULL,
|
258
|
+
|
259
|
+
`RankingCategoryHash2` int(10) unsigned NOT NULL,
|
260
|
+
|
261
|
+
`RankingCategoryHash3` int(10) unsigned NOT NULL,
|
262
|
+
|
263
|
+
`Ranking1` int(11) NOT NULL,
|
264
|
+
|
265
|
+
`Ranking2` int(11) NOT NULL,
|
266
|
+
|
267
|
+
`Ranking3` int(11) NOT NULL,
|
268
|
+
|
269
|
+
`PricePreFBA` int(11) DEFAULT NULL,
|
270
|
+
|
271
|
+
`PricePreSelf` int(11) DEFAULT NULL,
|
272
|
+
|
273
|
+
`PricePreUsedFBA` int(11) DEFAULT NULL,
|
274
|
+
|
275
|
+
`PricePreUsedSelf` int(11) DEFAULT NULL,
|
276
|
+
|
277
|
+
`PricePreCart` int(11) DEFAULT NULL,
|
278
|
+
|
279
|
+
`SetTypeSer` smallint(5) unsigned DEFAULT NULL,
|
280
|
+
|
281
|
+
`NoCommunicationTick` bigint(20) NOT NULL,
|
282
|
+
|
283
|
+
`Width` int(10) unsigned DEFAULT NULL,
|
284
|
+
|
285
|
+
`Height` int(10) unsigned DEFAULT NULL,
|
286
|
+
|
287
|
+
`Lenght` int(10) unsigned DEFAULT NULL,
|
288
|
+
|
289
|
+
`Weight` int(10) unsigned DEFAULT NULL,
|
290
|
+
|
291
|
+
`SizeTypeSer` smallint(5) unsigned NOT NULL,
|
292
|
+
|
293
|
+
`JAN2` varchar(13) COLLATE utf8mb4_general_ci NOT NULL,
|
294
|
+
|
295
|
+
`JAN3` varchar(13) COLLATE utf8mb4_general_ci NOT NULL,
|
296
|
+
|
297
|
+
`JAN4` varchar(13) COLLATE utf8mb4_general_ci NOT NULL,
|
298
|
+
|
299
|
+
`JAN5` varchar(13) COLLATE utf8mb4_general_ci NOT NULL,
|
300
|
+
|
301
|
+
`Model` varchar(64) COLLATE utf8mb4_general_ci DEFAULT NULL,
|
302
|
+
|
303
|
+
`RelationTick` bigint(20) NOT NULL,
|
304
|
+
|
305
|
+
`FlagSer` bit(32) NOT NULL,
|
306
|
+
|
307
|
+
PRIMARY KEY (`ASIN`),
|
308
|
+
|
309
|
+
KEY `price_idx` (`PriceSetTime`),
|
310
|
+
|
311
|
+
KEY `monoratetime_idx` (`MonorateSetTime`),
|
312
|
+
|
313
|
+
KEY `rank_idx` (`Ranking1`),
|
314
|
+
|
315
|
+
KEY `monorate_idx` (`MonorateBuyNum`),
|
316
|
+
|
317
|
+
KEY `fee_monorate_idx` (`FeeSetTime`,`MonorateBuyNum`),
|
318
|
+
|
319
|
+
KEY `jansindex` (`JAN`),
|
320
|
+
|
321
|
+
KEY `jan2index` (`JAN2`),
|
322
|
+
|
323
|
+
KEY `jan3index` (`JAN3`),
|
324
|
+
|
325
|
+
KEY `jan4index` (`JAN4`),
|
326
|
+
|
327
|
+
KEY `jan5index` (`JAN5`),
|
328
|
+
|
329
|
+
KEY `relationtick_idx` (`RelationTick`),
|
330
|
+
|
331
|
+
KEY `keepasort` (`MonorateSetTime`,`Ranking1`),
|
332
|
+
|
333
|
+
KEY `jan_relation_index` (`MonorateBuyNum`,`FlagSer`,`RelationTick`),
|
334
|
+
|
335
|
+
KEY `noc_idx` (`NoCommunicationTick`,`NotExit`,`SettingProductItem`,`ASIN`),
|
336
|
+
|
337
|
+
KEY `dhash_get_idx` (`MonorateBuyNum`,`ASIN`,`FlagSer`),
|
338
|
+
|
339
|
+
KEY `keepafilter` (`NotExit`,`MonorateSetTime`,`Ranking1`,`FlagSer`),
|
340
|
+
|
341
|
+
KEY `category_idx` (`CategorySetTime`,`FlagSer`,`NotExit`),
|
342
|
+
|
343
|
+
KEY `product_idx` (`ProductItemSetTime`,`FlagSer`,`NotExit`),
|
344
|
+
|
345
|
+
KEY `fee_idx` (`FeeSetTime`,`FlagSer`,`NotExit`),
|
346
|
+
|
347
|
+
KEY `dhash_idx` (`DHashSetTime`,`FlagSer`),
|
348
|
+
|
349
|
+
KEY `ureteru_get_index` (`MonorateBuyNum`,`SettingProductItem`,`FlagSer`,`ASIN`)
|
350
|
+
|
351
|
+
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci |------+
|
352
|
+
|
353
|
+
```
|
354
|
+
|
355
|
+
```ここに言語を入力
|
356
|
+
|
357
|
+
//analyzetime テーブル
|
358
|
+
|
359
|
+
| analyzetime | CREATE TABLE `analyzetime` (
|
360
|
+
|
361
|
+
`ASIN` varchar(10) COLLATE utf8mb4_general_ci NOT NULL,
|
362
|
+
|
363
|
+
`SiteName` varchar(256) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
|
364
|
+
|
365
|
+
`Tick` bigint(20) DEFAULT NULL,
|
366
|
+
|
367
|
+
PRIMARY KEY (`ASIN`,`SiteName`),
|
368
|
+
|
369
|
+
KEY `tick_idx` (`Tick`),
|
370
|
+
|
371
|
+
KEY `asin_idx` (`ASIN`),
|
372
|
+
|
373
|
+
KEY `tick_and_site_idx` (`Tick`,`SiteName`),
|
374
|
+
|
375
|
+
KEY `all_idx` (`Tick`,`ASIN`,`SiteName`)
|
206
376
|
|
207
377
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci |
|
208
378
|
|
209
|
-
|
210
|
-
|
211
|
-
```
|
379
|
+
```
|
1
force index for joinについての説明を修正
test
CHANGED
File without changes
|
test
CHANGED
@@ -4,7 +4,7 @@
|
|
4
4
|
|
5
5
|
force indexしてもインデックスだけで解決されず、Using join buffer (Block Nested Loop) となって速度が出ません。
|
6
6
|
|
7
|
-
joinに使用する列
|
7
|
+
joinに使用する列のインデックスを作成し、force index for joinの併用も試しましたが高速化できませんでした。
|
8
8
|
|
9
9
|
どうすればインデックスを使用して高速化できるのでしょうか。
|
10
10
|
|