MySQLのindexの張り方について

Question

下記のようなテーブルに５万件程度のデータが入っております。
データの中身ですが、映画のタイトル（日本語と英語）、登場人物が入力されております。
title_jp,title_en,characterそれぞれでユーザーが部分検索をする仕様です。

この場合、どのようにIndexを張ればよいでしょうか。
title_jp,title_en,characterの3つを複合Indexとするのがいいのではないかと考えておりますが、
いかがでしょうか。

id,title_jp,title_en,character,
3094,エアマスター,Air Master,Tachibana,
3095,エアマスター,Air Master,Tomiko Seriguchi,
3096,エアマスター,Air Master,Tsukio Taketsugu,
3097,エアマスター,Air Master,Yoshinori Konishi,
3098,エアマスター,Air Master,Yuukorin,

補足）
用途や構成など分かりにくい部分が多く申し訳ありませんでした。
まず構成ですが、参考のURLのように一つのフォームで映画のタイトルや出演者を同時に検索＋サジェスト機能を持ったものをイメージしていただければと思います。

ツタヤタイトル検索

この検索に引っかかるように、ユーザーが部分検索をしている際にAjaxでDBにアクセス、サジェスト結果を下に出るウインドウに表示させます。
ユーザーが該当の選択肢（該当タグ）を選び、「検索」ボタンを押すと、バインドされているtag_idを投げ、別に用意されているuser_idとtag_idが入力された中間テーブルをもとに同じタグをお気に入り登録しているユーザーの情報を「検索結果」として表示できます。

この「サジェスト」は例に挙げているtitle_jp,title_en,characterの全ての結果が必要となります。
その為、それぞれのデータを正規化し各自で保有しておくと、検索の際3つのテーブルをスキャン、該当のデータを見つけた後に3つのjoinが必要となるので逆に辛くなるのではと考えております。
知識がないので誤った考えかもしれません。

アドバイスを頂ければ幸いです。よろしくお願い致します。

Comments

[maisumakun]

「部分検索」とありますが、「各列ごとに部分一致で検索する」ということで間違いないでしょうか。

[supermaruetsu]

はい、その通りでございます。よろしくお願い致します。

Answer 1

MySQLで、column LIKE %ほげ%と部分一致検索をするとき、有効なインデックスはありません。ということで、インデックスが必要なら手段は次のいずれかです。

• MySQL上でフルテキストインデックスを構築して、MATCH AGAINSTで検索する
• 全文検索エンジンを別に入れる

Answer 2

見た感じindexの貼り方云々の前段階です
正規化のために映画テーブルをつくって

eiga_id,title_jp,title,en
1,エアマスター,Air Master

を外出しにして下さい

また本来俳優も俳優テーブルで
haiyu_id,character
1,Tomiko Seriguchi
2,Tsukio Taketsugu
3,Yoshinori Konishi
4,Yuukorin

とします。その上で
id,eiga_id,haiyu_id
3094,1,1
3095,1,2
3096,1,3
3097,1,4
3098,1,5

をとっておくのが妥当です。
この場合のインデックスはeiga_id,haiyu_idの複合インデックスが良いでしょう

sample

• テーブル作成

create table eiga(eiga_id int primary key,title_jp varchar(30),title_en varchar(30));
insert into eiga values(1,'エアマスター','Air Master');

create table haiyu(haiyu_id int primary key,`character` varchar(30));
insert into haiyu values
(1,'Tomiko Seriguchi'),
(2,'Tsukio Taketsugu'),
(3,'Yoshinori Konishi'),
(4,'Yuukorin');

create table shukei(id int primary key,eiga_id int,haiyu_id int,unique(eiga_id,haiyu_id));
insert into shukei values
(3094,1,1),
(3095,1,2),
(3096,1,3),
(3097,1,4),
(3098,1,5);

• 全データ表示

select t1.id,t2.title_jp,t2.title_en,t3.character
from shukei as t1
inner join eiga as t2 on t1.eiga_id=t2.eiga_id
inner join haiyu as t3 on t1.haiyu_id=t3.haiyu_id

• 特定の俳優を抽出

select t1.id,t2.title_jp,t2.title_en,t3.character
from shukei as t1
inner join eiga as t2 on t1.eiga_id=t2.eiga_id
inner join haiyu as t3 on t1.haiyu_id=t3.haiyu_id
and t3.character='Yoshinori Konishi'

Comments

[supermaruetsu]

私の理解不足によるコメントになってしまうかもしれませんが失礼します。これはタグサーチで「意図するタグを探し、自分のお気に入りとして別のテーブルに追加する」為のテーブルになります。別のテーブルとは、そのユーザーのお気に入りを管理する、user_idとtag_idで構成される中間テーブルになります。

言わんとしていることは、ひとつのフォームでタイトルor登場人物を部分検索し、該当のtag_idを見つけるには、テーブルを切り分けるのは手間になるのではないか？という事です。

[yambejp]

> ひとつのフォームでタイトルor登場人物を部分検索し、該当のtag_idを見つけるには、テーブルを切り分けるのはNGだということです。

ごめんなさい、状況がよくわかりません。
特殊なDBで管理しているならともかく、MySQLなど標準的なDBでは正規化しないという選択肢は
あまり考えられません。
もし正規化しない場合は、文字の微妙なブレ（大文字小文字・全角半角・不要なスペース文字）など
ヒューマンエラーにより、ただしい検索や集計ができなくなる可能性が高くなります。
それでもどうしてもというのであれば、ご提示の方式でやるのは構いません。

その場合はインデックスはたぶんなにをやっても最適にはならないので、
適当にトライアンドエラーでやるしかないでしょうね

[supermaruetsu]

ご回答ありがとうございます。
状況がうまく説明できるように、整理して補足を付け加えてみます！もし可能であれば、後ほどご確認頂ければ幸いです。

[yambejp]

補足をみましたが、正規化をしない理由になっていません。
なにを危惧しているのかわかりませんが、普通に正規化してインデックスを張ればいいと思います
（サンプル付けておきました）

[supermaruetsu]

再度のご回答ありがとうございました。BAは高評価の多い方にさせて頂きましたが、個人的に最も参考になる回答を頂けたと感じております。今後ともよろしくお願いいたします。

Answer 3

まずは正規化した方が良いと思います。
同じテーブルに同じデータをひたすら持つのは領域も無駄ですし
全て部分一致で探すのは遅くなるんじゃないかと・・・。

title_jp,title_en、characterは別テーブルに切り出して
IDでリレーションしておくのがオーソドックスパターンです。

それならば部分一致検索するデータ量も減り
あとはID完全一致で紐づけられるのでいいと思います。

Answer 4

インデックスによる高速化ということなら全文検索しかなさそうですけど、日本語が含まれるので、分かちまでを考慮するなら、他の全文検索エンジンってことになりそうですね。

別なアプローチとしては、インデックスにはなから期待せずにデータ件数を少なくすることを考えてはどうですか。

id,title_jp,title_en,character
3094,エアマスター,Air Master,'Tachibana','Tomiko Seriguchi','Tsukio Taketsugu',・・・

のように、俳優を配列で持つようなイメージです。

件数がどの程度圧縮されるか分かりませんが、少なくともタイトル部分に関しては、圧縮率が高ければそれなりに効果は期待できると思います。