MySQLで大量のデータに対して大量のキーワードでフィルタリングする方法

Question

MySQLで商品を扱うようなテーブルがあります。

CREATE TABLE tb_items
 (
  ID int NOT NULL,
  Name varchar(256),
  Brand varchar(32),
  Category varchar(32),
  Status boolean,
  PRIMARY KEY(ID),
  INDEX(Name, Brand, Category)
 )

上記カラムの意味は順に、「商品ID, 商品名, 製造元, カテゴリ, ステータス」となります。
このテーブルにはデータが数百万件入っているとします。

もう一つ、上記テーブルをキーワードでフィルタリングするためのキーワード用テーブルがあります。

CREATE TABLE tb_filter_keyword
 (
  ID int NOT NULL auto_increment,
  Keyword varchar(24),
  Type varchar(8),
  PRIMARY KEY(ID),
  INDEX(Keyword)
 )

上記カラムの意味は順に「キーワード, フィルターを掛けるカラム名」となります。
「フィルターを掛けるカラム名」とは、tb_itemsのどのカラムに対してフィルタリングするかという意味で、値は「Name, Brand, Category」が入ります。

そしてこのテーブルにはキーワードが1万件入っているとします。

---

ここから質問なのですが、数百万件あるtb_itemsの全行に対して、tb_filter_keywordのキーワードがName, Brand, Categoryのいずれかに含まれる場合、その行のStatusを1に、含まない場合は0にしたいです。

このようなクエリーはfor文で回してLIKE %keyword%で検索し、一つずつ更新していくしかないのでしょうか？

それとももっと効率の良い方法などがあるのでしょうか？

テンポラリーテーブルを作ったり、抽出した新たなテーブルを作ったり、など効率が良ければどのような方法でも構いません。商品数が数百万件、キーワードが1万件ありますので効率の良い方法をお聞きしたいです。

どうぞよろしくお願い致します。

Comments

[anonymouskawa]

両方のテーブルのPK、インデックス、ユニーク等の設定を教えていただけますか？

[退会済みユーザー]

ご指摘ありがとうございます。質問部分を修正致しました。
どうぞよろしくお願い致します。

Answer 1

tb_itemsを520447件と
tb_filter_keywordを65246件で試しました。

50万件なので、実際の性能は私が試した環境よりも劣ると思います。

update tb_items i
set i.Status = 0
where
not exists
  (select * from tb_filter_keyword f
   where case when f.Type = 'Name' then
   	i.Name like concat('%', f.keyword, '%')
   	when f.Type = 'Brand' then
   	i.Brand like concat('%', f.keyword, '%')
   	when f.type = 'Category' then
   	i.Category like concat('%', f.keyword, '%')
   end
	)

update tb_items i
set i.Status = 1
where
 exists
  (select * from tb_filter_keyword f
   where case when f.Type = 'Name' then
   	i.Name like concat('%', f.keyword, '%')
   	when f.Type = 'Brand' then
   	i.Brand like concat('%', f.keyword, '%')
   	when f.type = 'Category' then
   	i.Category like concat('%', f.keyword, '%')
   end
	)

時間がなくてあまり正確な検証をしていないので、
参考程度に見ていただければと思います。

Comments

[退会済みユーザー]

ありがとうございます！
わざわざ実データでチェックしていただいて感謝します！
非常に簡潔なクエリーで可能なのですね…。
私も実データを用意できたらチェックしたいと思います。
非常に助かりました、どうもありがとうございます！

Answer 2

先にすべてのSTATUSをFALSEにしておいて

下記SQLでフィルタに引っかかった商品だけTRUEにするとかはいかがでしょう?

UPDATE tb_items
	SET Status = TRUE
FROM (
	SELECT
		  SRC.ID
	FROM tb_items SRC
	JOIN tb_filter_keyword FIL
	/* フィルタで指定されたカラムにキーワードが含まれることのチェック */
	ON FIL.Type = 'Name' AND locate(FIL.Keyword, SRC.Name) > 0
	OR FIL.Type = 'Brand' AND locate(FIL.Keyword, SRC.Brand) > 0
	OR FIL.Type = 'Category' AND locate(FIL.keyword, SRC.Category) > 0
) TMP
WHERE ID = TMP.ID

---

UPDATE一回で済ませたいならこちら

UPDATE tb_items
	SET Status = TMP2.Status
FROM (
	SELECT
		  ID
		/* 最大値が0(フィルタにデータかかかっていない)場合にFALSE，それ以外はTRUEを設定 */
		, CASE WHEN MAX(TMP.INC) = 0 THEN FALSE ELSE TRUE END Status
	FROM (
		SELECT
			  ID
			/* フィルタに引っかかったデータIDに1を設定 */
			, 1 INC
		FROM tb_items SRC
		JOIN tb_filter_keyword FIL
		/* フィルタで指定されたカラムにキーワードが含まれることのチェック */
		ON FIL.Type = 'Name' AND locate(FIL.Keyword, SRC.Name) > 0
		OR FIL.Type = 'Brand' AND locate(FIL.Keyword, SRC.Brand) > 0
		OR FIL.Type = 'Category' AND locate(FIL.keyword, SRC.Category) > 0
		UNION ALL
		SELECT
			  ID
			/* すべてのIDでデータが見つからない場合の初期値(0)を設定 */
			, 0 
		FROM tb_items
	) TMP
	GROUP BY
		  ID
) TMP2
WHERE ID = TMP2.ID

Comments

[退会済みユーザー]

ありがとうございます！
UPDATE１回で済ませられるんですね…！
なかなかこのクエリーは思いつかないです…。
まだデータが用意できていないのでこちらのクエリーを試せませんが、近々用意できるのでぜひ試してみたいと思っています。
それとクエリーの意味も…。

とても助かりました！