MySql:複数のテーブルの結合でインデックスが使われず速度が出ないため、修正方法を教えてほしい

Question

select * from shop_ce
left join (select ASIN as dhashasin,SiteImgUrlHash as dhashimage,DHashDif from dhashdiff) as dhashdiff
on dhashdiff.dhashasin=shop_ce.ASIN&&
dhashdiff.dhashimage=shop_ce.ImageURLHash
left join (select ASIN,IsHide as UserAsinHide from userasin) as userasin using(ASIN)
left join (select ASIN,URLHash,IsHide as UserShopHide from usershop) as usershop
on
usershop.ASIN=shop_ce.ASIN&&usershop.URLHash=shop_ce.URLHash
where shop_ce.SearchBaseURL='https://kakaku.com/pricedown/';

MySqlで上記のコマンドを実行しているのですが、インデックスが使用されていないのか実行に数時間がかかります。

テーブル「shop_ce」に対して「dhashdiff」「userasin 」「usershop」を左結合し、最後にshop_ce.SearchBaseURLでwhereを行っています。
dhashdiffとの結合には列「ASIN」と「ImageURLHash」、
userasin との結合には列「ASIN」、
usershopとの結合には列「ASIN」と「URLHash」を使用しています。
shop_ceの複合インデックス「get_from_shop」はASIN、ImageURLHash、URLHash、SearchBaseURLを順番に使用しているためこちらのインデックスを使用できるはずです。
shop_ceに結合する各テーブルも、結合に使用するためのインデックスを作成しています。
文字列の列は文字コードと長さも揃えているためインデックスを使用する条件はそろっているはずです。

どうすれば高速化できるのでしょうか。
ご教示いただけますと幸いです。

explain

+----+-------------+-----------+------------+--------+-----------------------------------------------------------------+-----------------+---------+---------------------------------------------------+---------+----------+-----------------------+
| id | select_type | table     | partitions | type   | possible_keys                                                   | key             | key_len | ref                                               | rows    | filtered | Extra                 |
+----+-------------+-----------+------------+--------+-----------------------------------------------------------------+-----------------+---------+---------------------------------------------------+---------+----------+-----------------------+
|  1 | SIMPLE      | shop_ce   | NULL       | ref    | search_base_url                                                 | search_base_url | 1026    | const                                             | 2626040 |   100.00 | Using index condition |
|  1 | SIMPLE      | dhashdiff | NULL       | ref    | image_asin,asin,analyze_join_index,set_priority,asin_image_hash | asin_image_hash | 16      | imusedb.shop_ce.ASIN,imusedb.shop_ce.ImageURLHash |       1 |   100.00 | NULL                  |
|  1 | SIMPLE      | userasin  | NULL       | eq_ref | PRIMARY                                                         | PRIMARY         | 12      | imusedb.shop_ce.ASIN                              |       1 |   100.00 | NULL                  |
|  1 | SIMPLE      | usershop  | NULL       | ref    | PRIMARY,asin,asin_url_hash                                      | asin_url_hash   | 16      | imusedb.shop_ce.ASIN,imusedb.shop_ce.URLHash      |       1 |   100.00 | NULL                  |
+----+-------------+-----------+------------+--------+-----------------------------------------------------------------+-----------------+---------+---------------------------------------------------+---------+----------+-----------------------+

create table

shop_ce

| shop_ce | CREATE TABLE `shop_ce` (
  `ShopName` varchar(256) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL,
  `ShopNameOpt` varchar(256) COLLATE utf8mb4_general_ci DEFAULT NULL,
  `SetTime` bigint(20) DEFAULT NULL,
  `ASIN` varbinary(10) NOT NULL,
  `SiteName` varchar(256) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL,
  `SiteImgUrl` varbinary(2083) DEFAULT NULL,
  `Name` varchar(256) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL,
  `NameOpt` varchar(256) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL,
  `URL` varbinary(2083) NOT NULL,
  `ImageURL` varbinary(2083) DEFAULT NULL,
  `Price` int(11) DEFAULT NULL,
  `Postage` int(11) DEFAULT NULL,
  `Used` bit(1) NOT NULL,
  `DHashSetTime` bigint(20) NOT NULL,
  `DHashDif` smallint(5) unsigned NOT NULL,
  `IsJAN` bit(1) NOT NULL,
  `PointRate` float(3,3) unsigned NOT NULL,
  `SellerName` varchar(128) COLLATE utf8mb4_general_ci NOT NULL,
  `Page` int(11) NOT NULL,
  `SearchBaseURL` varbinary(1024) NOT NULL,
  `URLHash` int(10) unsigned NOT NULL,
  `ImageURLHash` int(10) unsigned NOT NULL,
  PRIMARY KEY (`SellerName`,`ASIN`,`URL`),
  KEY `site_shop` (`SiteName`,`ShopName`),
  KEY `asin_name` (`ASIN`,`SellerName`),
  KEY `asin` (`ASIN`),
  KEY `asin_image` (`ASIN`,`ImageURL`),
  KEY `sellerName` (`SellerName`),
  KEY `asin_url_seller` (`ASIN`,`URL`,`SellerName`),
  KEY `search_base_url` (`SearchBaseURL`),
  KEY `asin_image_site` (`ASIN`,`ImageURL`,`SiteName`),
  KEY `asin_search_base_url` (`ASIN`,`SearchBaseURL`),
  KEY `get_from_seller` (`ASIN`,`ImageURLHash`,`URLHash`,`SellerName`),
  KEY `get_from_shop` (`ASIN`,`ImageURLHash`,`URLHash`,`SearchBaseURL`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci |

dhashdiff

| dhashdiff | CREATE TABLE `dhashdiff` (
  `SiteImgUrl` varbinary(2083) NOT NULL,
  `DHashDif` smallint(5) unsigned NOT NULL,
  `SetTime` bigint(19) unsigned NOT NULL,
  `ASIN` varbinary(10) NOT NULL,
  `SiteName` varchar(64) COLLATE utf8mb4_general_ci NOT NULL,
  `UrlAndASINHash` int(10) unsigned NOT NULL,
  `SiteImgUrlHash` int(10) unsigned NOT NULL,
  PRIMARY KEY (`SiteImgUrl`,`ASIN`,`SiteName`),
  KEY `image_asin` (`ASIN`,`SiteImgUrl`),
  KEY `settime_index` (`SetTime`),
  KEY `asin` (`ASIN`),
  KEY `analyze_join_index` (`ASIN`,`SiteImgUrl`,`SiteName`,`SetTime`),
  KEY `set_priority` (`ASIN`,`SiteImgUrl`,`SiteName`,`DHashDif`),
  KEY `asin_url_hash` (`UrlAndASINHash`),
  KEY `asin_image_hash` (`ASIN`,`SiteImgUrlHash`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci |

userasin

| userasin | CREATE TABLE `userasin` (
  `ASIN` varbinary(10) NOT NULL,
  `IsHide` bit(1) NOT NULL,
  `HideTime` bigint(20) NOT NULL,
  `HideTimeSetTime` bigint(20) NOT NULL,
  `UserSettingTime` bigint(20) NOT NULL,
  `Comment` varchar(252) COLLATE utf8mb4_general_ci NOT NULL,
  PRIMARY KEY (`ASIN`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci |

usershop

| usershop | CREATE TABLE `usershop` (
  `ASIN` varbinary(10) NOT NULL,
  `IsHide` bit(1) NOT NULL,
  `HideTime` bigint(20) NOT NULL,
  `HideTimeSetTime` bigint(20) NOT NULL,
  `UserSettingTime` bigint(20) NOT NULL,
  `Comment` varchar(252) COLLATE utf8mb4_general_ci NOT NULL,
  `URL` varbinary(2083) NOT NULL,
  `URLHash` int(10) unsigned NOT NULL,
  PRIMARY KEY (`ASIN`,`URL`),
  KEY `asin` (`ASIN`),
  KEY `asin_url_hash` (`ASIN`,`URLHash`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci |

Comments

[yambejp]

left joinが必須の仕組みなのでしょうか？
left joinではパフォーマンスはあまり期待できないですが・・・
left join後にwhereするのも効率的ではないかもしれません
（where句での絞り込みがリレーションに絡まないみたいですし）

[FKM]

結合、まして不完全結合の場合、select対象のカラム指定にアスタリスクはやめた方がいいですよ。テーブル名.カラム名を逐一明示しないと結合している全部のテーブルを場当たりで調べることになってしまいます。

[yonotsui]

>left joinが必須の仕組みなのでしょうか？
left joinせず個別に取得する手も考えましたが、取得するshop_ceの行数が膨大なため断念しました。
現状はleft joinなどの外部結合でしか実装できないものと考えています。
>left join後にwhereするのも効率的ではないかもしれません
おっしゃる通りwhereを先にしたほうが効率的でした。
順序を変えることで改善できました。

>結合、まして不完全結合の場合、select対象のカラム指定にアスタリスクはやめた方がいいですよ。テーブル名.カラム名を逐一明示しないと結合している全部のテーブルを場当たりで調べることになってしまいます。
ワイルドカードは負荷がかかるのですね。
恥ずかしながら全く把握しておりませんでした。
列名を直接指定するように修正させていただきました。

おかげさまで問題を改善することができました。
お二人ともアドバイス頂きありがとうございました。

Answer 1

下記のコマンドに修正しました。

with
shop_ce as 
(select * from shop_ce where shop_ce.SearchBaseURL='https://kakaku.com/pricedown/')

select shop_ce.ShopName,shop_ce.SellerName,shop_ce.ShopNameOpt,shop_ce.SetTime,shop_ce.Page,shop_ce.SearchBaseURL,shop_ce.URLHash,shop_ce.ImageURLHash,shop_ce.ASIN,shop_ce.SiteName,shop_ce.SiteImgUrl,shop_ce.Name,shop_ce.NameOpt,shop_ce.URL,shop_ce.ImageURL,shop_ce.Price,shop_ce.Postage,shop_ce.Used,shop_ce.DHashSetTime,shop_ce.DHashDif,shop_ce.IsJAN,shop_ce.PointRate,
userasin.IsHide as UserAsinHide,
usershop.IsHide as UserShopHide,
dhashdiff.DHashDif as DHashDifWork
from shop_ce
left join dhashdiff
on dhashdiff.ASIN=shop_ce.ASIN&&
dhashdiff.SiteImgUrlHash=shop_ce.ImageURLHash
left join userasin
on shop_ce.ASIN=userasin.ASIN
left join usershop
on usershop.ASIN=shop_ce.ASIN&&
usershop.URLHash=shop_ce.URLHash;

shop_ceのSearchBaseURLを定数でwhereしてから左結合するように修正しました。
そのためにshop_ceの複合インデックスをSearchBaseURL,ASIN,ImageURLHash,URLHashの順に変更しました。
SELECT文の評価順序に従ってインデックスを作成するよりこちらの方が早いためです。
現状処理時間は1秒前後に納まりました。
高速化は不十分だと思いますが許容範囲ではあるので、この状態で運用します。
みなさまアドバイス頂きありがとうございました。