MySQLでindexを一時的に利用したい

indexの貼り方がa, bの複合キーとなっていて
bに直接index貼られてない状態で
bについて重複しているデータの確認をする必要があったため
下記のようなクエリを流したところ

mysql> select b, count(id) count from HOGE group by b having count(id) > 1;

bでgroup byしようとしたらめっちゃ時間かかる。
(あまりに遅いので途中でキャンセルした。)

複合インデックスでは順になっていないとインデックス利用できないので
aだけならともかく、bだけだとちゃんと効かない

ちなみにexplainで確認するとこんな感じ

mysql> explain select b, count(id) from HOGE group by b having count(id) > 1;

id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	HOGE	index	key_a_b, index_a_b	key_a_b	xxx	NULL	xxxxxxx	Using index; Using temporary; Using filesort

Using temporaryとUsing filesortがついてしまっている。
ちなみにa,bでgroup byすればちゃんとインデックス効いている事は確認済み

mysql> explain select a, b, count(id) from HOGE group by a, b having count(id) > 1;

id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	HOGE	index	key_a_b, index_a_b	key_a_b	xxx	NULL	xxxxxxx	Using index

とはいえ調査のためだけなのでできれば元のテーブルにインデックス追加はしたくない。

temporary tableの作成

仕方ないので改めてbだけにindex貼ったtemporary tableを作って確認する。

create temporary table `tmp_table` 
(
  a bigint(20),
  b varchar(255),
  index b_index(b)
)
engine = MyISAM
as
(
  select HOGE.a, HOGE.b
  from HOGE
);

InnoDBだとテンポラリテーブル作成に時間かかったので
engineにMyISAMを指定している。
調査用に一時的に使うだけのテーブルだし
トランザクションとかは特に使わないので問題はないはず。

で重複しているbがあるかチェック

select b, count(b) from tmp_table group by b having count(b) > 1;

ちなみにexplainの結果は下記

mysql> explain select b, count(b) from tmp_table group by b having count(b) > 1;

id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	tmp_table	index	b_index	b_index	xxx	NULL	xxxxxxx	Using index

ちゃんとUsing indexだけになってる。
ちなみに重複はめっちゃありました。

参考

MySQL 5.6 リファレンスマニュアル