質問編集履歴
2
STRAIGHT_JOIN を試した結果を追記
title
CHANGED
|
File without changes
|
body
CHANGED
|
@@ -19,23 +19,19 @@
|
|
|
19
19
|
inner join asin using(ASIN)
|
|
20
20
|
where SellerName=' Dig outer';
|
|
21
21
|
|
|
22
|
-
+----+-------------+---------+------------+------+----------------------------------------------------------------------------------------------------+---------+---------+-------------------------+-----------+----------+-------------+
|
|
23
22
|
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
|
|
24
23
|
+----+-------------+---------+------------+------+----------------------------------------------------------------------------------------------------+---------+---------+-------------------------+-----------+----------+-------------+
|
|
25
24
|
| 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 |
|
|
26
25
|
| 1 | SIMPLE | shop_ce | NULL | ref | PRIMARY,asin_name,asin,asin_image | PRIMARY | 526 | const,imusedb.asin.ASIN | 6 | 100.00 | Using where |
|
|
27
|
-
+----+-------------+---------+------------+------+----------------------------------------------------------------------------------------------------+---------+---------+-------------------------+-----------+----------+-------------+
|
|
28
26
|
|
|
29
27
|
//force indexの場合
|
|
30
28
|
select * from shop_ce force index(asin_name)
|
|
31
29
|
inner join asin using(ASIN)
|
|
32
30
|
where SellerName='Dig outer';
|
|
33
|
-
+----+-------------+---------+------------+------+----------------------------------------------------------------------------------------------------+-----------+---------+-------------------------+-----------+----------+-----------------------+
|
|
34
31
|
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
|
|
35
32
|
+----+-------------+---------+------------+------+----------------------------------------------------------------------------------------------------+-----------+---------+-------------------------+-----------+----------+-----------------------+
|
|
36
33
|
| 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 |
|
|
37
34
|
| 1 | SIMPLE | shop_ce | NULL | ref | asin_name | asin_name | 526 | imusedb.asin.ASIN,const | 2 | 100.00 | Using index condition |
|
|
38
|
-
+----+-------------+---------+------------+------+----------------------------------------------------------------------------------------------------+-----------+---------+-------------------------+-----------+----------+-----------------------+
|
|
39
35
|
```
|
|
40
36
|
### shop_ceテーブルのCreateTable
|
|
41
37
|
```ここに言語を入力
|
|
@@ -161,4 +157,16 @@
|
|
|
161
157
|
KEY `asin_join_flagser` (`ASIN`,`FlagSer`),
|
|
162
158
|
KEY `self_buy` (`FlagSer`,`PriceCart`,`PriceSelf`,`PriceSelfNoShip`,`FeeRate`,`FixFee`,`PricePreCart`,`PriceFBA`)
|
|
163
159
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci |
|
|
160
|
+
```
|
|
161
|
+
|
|
162
|
+
・追記：STRAIGHT_JOINの場合
|
|
163
|
+
```
|
|
164
|
+
explain select STRAIGHT_JOIN *
|
|
165
|
+
from shop_ce
|
|
166
|
+
left join asin using(ASIN)
|
|
167
|
+
where SellerName='Dig outer';
|
|
168
|
+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
|
|
169
|
+
+----+-------------+---------+------------+------+----------------------------------------------------------------------------------------------------+---------+---------+-------+-----------+----------+------------------------------------------------------+
|
|
170
|
+
| 1 | SIMPLE | shop_ce | NULL | ref | PRIMARY,sellerName | PRIMARY | 514 | const | 1 | 100.00 | NULL |
|
|
171
|
+
| 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) |
|
|
164
172
|
```
|
1
SQLのバージョンを記載
title
CHANGED
|
File without changes
|
body
CHANGED
|
@@ -11,6 +11,8 @@
|
|
|
11
11
|
なぜインデックスが使用されないのでしょうか。
|
|
12
12
|
ご教示いただけますと幸いです。
|
|
13
13
|
|
|
14
|
+
追記：MySQLのバージョンは8.0.18です。
|
|
15
|
+
|
|
14
16
|
##explain
|
|
15
17
|
```ここに言語を入力
|
|
16
18
|
select * from shop_ce
|