質問編集履歴
2
STRAIGHT_JOIN を試した結果を追記
test
CHANGED
|
File without changes
|
test
CHANGED
|
@@ -40,18 +40,14 @@
|
|
|
40
40
|
|
|
41
41
|
|
|
42
42
|
|
|
43
|
+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
|
|
44
|
+
|
|
43
45
|
+----+-------------+---------+------------+------+----------------------------------------------------------------------------------------------------+---------+---------+-------------------------+-----------+----------+-------------+
|
|
44
46
|
|
|
45
|
-
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
|
|
46
|
-
|
|
47
|
-
+----+-------------+---------+------------+------+----------------------------------------------------------------------------------------------------+---------+---------+-------------------------+-----------+----------+-------------+
|
|
48
|
-
|
|
49
47
|
| 1 | SIMPLE | asin | NULL | ALL | PRIMARY,ureteru_get_index,get_analyze_time_idx,price_asin_idx,ureteru_get_index2,asin_join_flagser | NULL | NULL | NULL | 127656540 | 100.00 | NULL |
|
|
50
48
|
|
|
51
49
|
| 1 | SIMPLE | shop_ce | NULL | ref | PRIMARY,asin_name,asin,asin_image | PRIMARY | 526 | const,imusedb.asin.ASIN | 6 | 100.00 | Using where |
|
|
52
50
|
|
|
53
|
-
+----+-------------+---------+------------+------+----------------------------------------------------------------------------------------------------+---------+---------+-------------------------+-----------+----------+-------------+
|
|
54
|
-
|
|
55
51
|
|
|
56
52
|
|
|
57
53
|
//force indexの場合
|
|
@@ -62,18 +58,14 @@
|
|
|
62
58
|
|
|
63
59
|
where SellerName='Dig outer';
|
|
64
60
|
|
|
61
|
+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
|
|
62
|
+
|
|
65
63
|
+----+-------------+---------+------------+------+----------------------------------------------------------------------------------------------------+-----------+---------+-------------------------+-----------+----------+-----------------------+
|
|
66
64
|
|
|
67
|
-
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
|
|
68
|
-
|
|
69
|
-
+----+-------------+---------+------------+------+----------------------------------------------------------------------------------------------------+-----------+---------+-------------------------+-----------+----------+-----------------------+
|
|
70
|
-
|
|
71
65
|
| 1 | SIMPLE | asin | NULL | ALL | PRIMARY,ureteru_get_index,get_analyze_time_idx,price_asin_idx,ureteru_get_index2,asin_join_flagser | NULL | NULL | NULL | 127656540 | 100.00 | NULL |
|
|
72
66
|
|
|
73
67
|
| 1 | SIMPLE | shop_ce | NULL | ref | asin_name | asin_name | 526 | imusedb.asin.ASIN,const | 2 | 100.00 | Using index condition |
|
|
74
68
|
|
|
75
|
-
+----+-------------+---------+------------+------+----------------------------------------------------------------------------------------------------+-----------+---------+-------------------------+-----------+----------+-----------------------+
|
|
76
|
-
|
|
77
69
|
```
|
|
78
70
|
|
|
79
71
|
### shop_ceテーブルのCreateTable
|
|
@@ -325,3 +317,27 @@
|
|
|
325
317
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci |
|
|
326
318
|
|
|
327
319
|
```
|
|
320
|
+
|
|
321
|
+
|
|
322
|
+
|
|
323
|
+
・追記：STRAIGHT_JOINの場合
|
|
324
|
+
|
|
325
|
+
```
|
|
326
|
+
|
|
327
|
+
explain select STRAIGHT_JOIN *
|
|
328
|
+
|
|
329
|
+
from shop_ce
|
|
330
|
+
|
|
331
|
+
left join asin using(ASIN)
|
|
332
|
+
|
|
333
|
+
where SellerName='Dig outer';
|
|
334
|
+
|
|
335
|
+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
|
|
336
|
+
|
|
337
|
+
+----+-------------+---------+------------+------+----------------------------------------------------------------------------------------------------+---------+---------+-------+-----------+----------+------------------------------------------------------+
|
|
338
|
+
|
|
339
|
+
| 1 | SIMPLE | shop_ce | NULL | ref | PRIMARY,sellerName | PRIMARY | 514 | const | 1 | 100.00 | NULL |
|
|
340
|
+
|
|
341
|
+
| 1 | SIMPLE | asin | NULL | ALL | PRIMARY,ureteru_get_index,get_analyze_time_idx,price_asin_idx,ureteru_get_index2,asin_join_flagser | NULL | NULL | NULL | 127663023 | 100.00 | Range checked for each record (index map: 0x5A40001) |
|
|
342
|
+
|
|
343
|
+
```
|
1
SQLのバージョンを記載
test
CHANGED
|
File without changes
|
test
CHANGED
|
@@ -24,6 +24,10 @@
|
|
|
24
24
|
|
|
25
25
|
|
|
26
26
|
|
|
27
|
+
追記：MySQLのバージョンは8.0.18です。
|
|
28
|
+
|
|
29
|
+
|
|
30
|
+
|
|
27
31
|
##explain
|
|
28
32
|
|
|
29
33
|
```ここに言語を入力
|