ブランクが多いカラムをJOINする際のインデックスの効かせ方

Question

MySQLのバージョンは8.0.17になります。
下記のようなSQLを実行しようとしております。

SELECT * FROM abc_data a 
LEFT JOIN user_data b ON a.abc_code = b.abc_code 

こちらを実行するのに現在数分程度かかっております。
それぞれのテーブルのおおよそのレコード数は下記の通りになります。
abc_data:7560行程度
user_data:86000行程度

上記のSQLの実行計画ですが下記の通りとなります。

id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	a		ALL					7560	100
1	SIMPLE	b		ALL	abc_code				87656	100	Range checked for each record (index map: 0x4)

また、user_dataのabc_codeは
abc_codeあり:3200行程度
abc_codeがブランク:82800行程度
となっております。(abc_codeなしのレコードはabc_codeがnullではなくブランクです。)

user_dataテーブルのabc_codeがブランクが多すぎるせいで
abc_codeのインデックスが使用されずuser_dataのフルスキャンが走るせいでレスポンスが低下していると推測しております。

そこで下記のようにJOINする際にabc_codeを使用するようにしてみました。

SELECT * FROM abc_data a 
LEFT JOIN user_data b FORCE INDEX FOR JOIN (abc_code)　ON a.abc_code = b.abc_code 

上記の実行結果がこちらになります。

id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	a		ALL					7560	100
1	SIMPLE	b		ALL	abc_code				87656	100	Range checked for each record (index map: 0x4)

結果、先ほどと変わらずuser_dataのフルスキャンが走る形となっております。

可能であればuser_dataの値を変えたりすることなくインデックスを効かせ
レスポンスを改善させたいのですが、何か方法はございますでしょうか。

不要な情報を全て削ったabc_data,user_dataを作成するSQLは下記になります。
こちらで上述のSQLを実行した際にuser_dataのabc_codeのインデックスを使用し検索が実行したい、という要望になります。

CREATE TABLE `abc_data`  (
  `rowid` int(11) NOT NULL AUTO_INCREMENT,
  `abc_code` varchar(10) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NOT NULL DEFAULT '',
  PRIMARY KEY (`rowid`) USING BTREE,
  INDEX `abc_code`(`abc_code`) USING BTREE
) ENGINE = MyISAM AUTO_INCREMENT = 7820 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_bin ROW_FORMAT = Dynamic;

INSERT INTO `abc_data` VALUES (1, 'ABCD');
INSERT INTO `abc_data` VALUES (2, 'EFGH');
INSERT INTO `abc_data` VALUES (3, 'SETH');
INSERT INTO `abc_data` VALUES (4, 'EYUK');
INSERT INTO `abc_data` VALUES (5, 'ROPN');
INSERT INTO `abc_data` VALUES (6, 'SEKB');
INSERT INTO `abc_data` VALUES (7, 'HORP');
INSERT INTO `abc_data` VALUES (8, 'XKEM');
INSERT INTO `abc_data` VALUES (9, 'EOIF');
INSERT INTO `abc_data` VALUES (10, 'EMSO');

CREATE TABLE `user_data`  (
  `userid` int(11) NOT NULL AUTO_INCREMENT,
  `abc_code` varchar(10) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL DEFAULT '',
  PRIMARY KEY (`userid`) USING BTREE,
  INDEX `abc_code`(`abc_code`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 100400622 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci ROW_FORMAT = Dynamic;

INSERT INTO `user_data` VALUES (1, '');
INSERT INTO `user_data` VALUES (2, '');
INSERT INTO `user_data` VALUES (3, '');
INSERT INTO `user_data` VALUES (4, '');
INSERT INTO `user_data` VALUES (5, '');
INSERT INTO `user_data` VALUES (6, '');
INSERT INTO `user_data` VALUES (7, '');
INSERT INTO `user_data` VALUES (8, '');
INSERT INTO `user_data` VALUES (9, '');
INSERT INTO `user_data` VALUES (10, '');
INSERT INTO `user_data` VALUES (11, '');
INSERT INTO `user_data` VALUES (12, '');
INSERT INTO `user_data` VALUES (13, '');
INSERT INTO `user_data` VALUES (14, '');
INSERT INTO `user_data` VALUES (15, '');
INSERT INTO `user_data` VALUES (16, '');
INSERT INTO `user_data` VALUES (17, '');
INSERT INTO `user_data` VALUES (18, '');
INSERT INTO `user_data` VALUES (19, '');
INSERT INTO `user_data` VALUES (20, '');
INSERT INTO `user_data` VALUES (21, '');
INSERT INTO `user_data` VALUES (22, '');
INSERT INTO `user_data` VALUES (23, '');
INSERT INTO `user_data` VALUES (24, '');
INSERT INTO `user_data` VALUES (25, '');
INSERT INTO `user_data` VALUES (26, '');
INSERT INTO `user_data` VALUES (27, '');
INSERT INTO `user_data` VALUES (28, '');
INSERT INTO `user_data` VALUES (29, '');
INSERT INTO `user_data` VALUES (30, '');
INSERT INTO `user_data` VALUES (31, '');
INSERT INTO `user_data` VALUES (32, '');
INSERT INTO `user_data` VALUES (33, '');
INSERT INTO `user_data` VALUES (34, '');
INSERT INTO `user_data` VALUES (35, '');
INSERT INTO `user_data` VALUES (36, '');
INSERT INTO `user_data` VALUES (37, '');
INSERT INTO `user_data` VALUES (38, '');
INSERT INTO `user_data` VALUES (39, '');
INSERT INTO `user_data` VALUES (40, '');
INSERT INTO `user_data` VALUES (41, '');
INSERT INTO `user_data` VALUES (42, '');
INSERT INTO `user_data` VALUES (43, '');
INSERT INTO `user_data` VALUES (44, '');
INSERT INTO `user_data` VALUES (45, '');
INSERT INTO `user_data` VALUES (46, '');
INSERT INTO `user_data` VALUES (47, '');
INSERT INTO `user_data` VALUES (48, '');
INSERT INTO `user_data` VALUES (49, '');
INSERT INTO `user_data` VALUES (50, '');
INSERT INTO `user_data` VALUES (51, '');
INSERT INTO `user_data` VALUES (52, '');
INSERT INTO `user_data` VALUES (53, '');
INSERT INTO `user_data` VALUES (54, '');
INSERT INTO `user_data` VALUES (55, '');
INSERT INTO `user_data` VALUES (56, '');
INSERT INTO `user_data` VALUES (57, '');
INSERT INTO `user_data` VALUES (58, '');
INSERT INTO `user_data` VALUES (59, '');
INSERT INTO `user_data` VALUES (60, '');
INSERT INTO `user_data` VALUES (61, '');
INSERT INTO `user_data` VALUES (62, '');
INSERT INTO `user_data` VALUES (63, '');
INSERT INTO `user_data` VALUES (64, '');
INSERT INTO `user_data` VALUES (65, '');
INSERT INTO `user_data` VALUES (66, '');
INSERT INTO `user_data` VALUES (67, 'DKEO');
INSERT INTO `user_data` VALUES (70, 'DKEO');
INSERT INTO `user_data` VALUES (68, 'EMSK');
INSERT INTO `user_data` VALUES (69, 'LOES');

Comments

[yambejp]

データ数はすくなくてよいので、かんたんなサンプルを上げてください
create table+insertで例示されるとよいでしょう
（不要なカラムはばっさり削ってください）

[hihijiji]

サブクエリでブランクレコードを除いてからJOINしてみては？

[m.ts10806]

設計段階から見直したほうがパフォーマンス向上は見込めるかと思いますが、そこは考慮せず食えりだけでなんとかしたいということでしょうか（インデックス再検討もなし？）

[Rio_1201]

設計段階から見直した方がよいというのは最もなのですが
工数の都合上、前任の作ったものを誤魔化しながら対応するしかないのが現状です。
クエリのみ、もしくはインデックスの変更程度で対応することが出来れば、と思っております。

Answer 1

そもそもなんで態々空白を入れているんですか？
Nullなら、インデックスの件数も少なくなって早くなりそうですけど。

データを変えずにだと、以下のSQLが遅いようなら改善は難しいと思います。

select * from user_data where abc_code > ' '

期待できる速度なら、上記をインラインビューにしたものと結合してみて下さい。

追記

ふと思いつきましたが、空白をNullに置き換えた仮想列を作成し、その仮想列にインデックスを付ければ高速化されるかもしれません。
関数インデックスが使えるバージョンなら、その方が手軽です。

Comments

[Rio_1201]

ありがとうございます。
やはり小手先での解決が難しそうでしたのでわざわざ空白を入れるのをやめNULLで
根本的に解決する方向で対応しようと思います。

[sazi]

思い付きですけど、追記しました。

Answer 2

indexを効かせるだけであればabc_dataのインデックスを

INDEX abc_code(abc_code) USING BTREE

をやめて、こうしてください

INDEX `rowid_abc_code`(`rowid`,`abc_code`) USING BTREE

Comments

[yambejp]

ただしこれが高速化につながるかどうかは別の話です

[Rio_1201]

ありがとうございます。
ただ、おっしゃっておられる通りやはり高速化にはつながらず別の方法で対応しようと思います。

[yambejp]

nullにするくらいなら、別テーブルに逃してやれば良いと思います