FULLTEXT INDEXとは
MySQL や MariaDB などのデータベースで提供される全文検索のこと
値が一部一致している結果を取得できる
- メリット
- 大規模なデータから特定の単語を効率的に探すときに有効
- デメリット
LIKE検索とは
SQLで使用できる部分一致検索のこと
何を検証するのか
FULLTEXT INDEX検索とLIKE検索を実施し、検索速度にどのくらい違いが出るかを以下の観点で検証します。
- レコード数の速度比較
- 大規模データ(レコード100万件)と小規模データ(レコード100件)で検索速度を比較
- 文字数の速度比較
- 各レコードの文字数が多い場合（1000文字）と少ない場合（10文字）で検索速度を比較
-
重複文字の速度比較
検証開始
テストデータの作成にはストアドプロシージャを使用しました。
以下の手順でテストデータ作成から検証まで行っています。
- データ挿入用のテーブル作成
- ストアドプロシージャでテストデータ作成のSQL記述
- データを生成
- パフォーマンス比較
- FULLTEXT INDEXとLIKE検索を実施し、速度比較
レコード数での比較
テストデータの準備
- `CREATE DATABASE IF NOT EXISTS TestDB;`
- `USE TestDB;`
- レコード数が多い場合
CREATE TABLE large_dataset (
id INT AUTO_INCREMENT PRIMARY KEY,
content TEXT,
FULLTEXT(content)
);
- レコード数が少ない場合
CREATE TABLE small_dataset (
id INT AUTO_INCREMENT PRIMARY KEY,
content TEXT,
FULLTEXT(content)
);
- ストアドプロシージャ作成（SQLの条件などをまとめており、今回はデータ生成に使用）
DELIMITER //
# レコード数が多い場合
CREATE PROCEDURE generate_large_dataset(IN num_records INT)
BEGIN
DECLARE i INT DEFAULT 0;
DECLARE sample_text VARCHAR(255);
WHILE i < num_records DO
SET sample_text = CONCAT('サンプルテキスト ', i, ' ランダム文字 ', RAND());
INSERT INTO large_dataset (content) VALUES (sample_text);
SET i = i + 1;
END WHILE;
END //
# レコード数が少ない場合
CREATE PROCEDURE generate_small_dataset(IN num_records INT)
BEGIN
DECLARE i INT DEFAULT 0;
DECLARE sample_text VARCHAR(255);
WHILE i < num_records DO
SET sample_text = CONCAT('サンプルテキスト ', i, ' ランダム文字 ', RAND());
INSERT INTO small_dataset (content) VALUES (sample_text);
SET i = i + 1;
END WHILE;
END //
DELIMITER ;
- レコード数が多いデータ（100万件）
CALL generate_large_dataset(1000000);
- レコード数が少ないデータ（100件）
CALL generate_small_dataset(100);
レコード数の検証と結果
検証
- プロファイリングを有効にする
SET profiling = 1;
- large_datasetでのFULLTEXT INDEX検索
SELECT * FROM large_dataset WHERE MATCH(content) AGAINST('"サンプルテキスト 999995"' IN BOOLEAN MODE);
- large_datasetでのLIKE検索
SELECT * FROM large_dataset WHERE content LIKE '%サンプルテキスト 999995%';
- small_datasetでのFULLTEXT INDEX検索
SELECT * FROM small_dataset WHERE MATCH(content) AGAINST('"サンプルテキスト 90"' IN BOOLEAN MODE);
- small_datasetでのLIKE検索
SELECT * FROM small_dataset WHERE content LIKE '%サンプルテキスト 90%';
- プロファイリング結果を表示
SHOW PROFILES;
結果
大規模データならFULLTEXT INDEXが速く、小規模データならLIKE検索が速い
- レコード100万件ならFULLTEXT INDEXが速い
- FULLTEXT INDEX検索: 0.706秒
- LIKE検索: 0.887秒
|Query_ID | Duration | Query |
|1005153 | 0.70630800 | SELECT * FROM large_dataset WHERE MATCH(content) AGAINST('"サンプルテキスト 999995"' IN BOOLEAN MODE) |
|1005154 | 0.88710600 | SELECT * FROM large_dataset WHERE content LIKE '%サンプルテキスト 999995%' |
-
レコード100件ならLIKE検索が速い
FULLTEXT INDEX検索: 0.048秒
LIKE検索: 0.001秒
| Query_ID| Duration | Query |
| 24| 0.04868400 | SELECT * FROM small_dataset WHERE MATCH(content) AGAINST('"サンプルテキスト 90"' IN BOOLEAN MODE) |
| 25| 0.00117700 | SELECT * FROM small_dataset WHERE content LIKE '%サンプルテキスト 90%' |
文字数での比較
レコードを10000件で検証しましたが、レコード数に結果が依存していたためレコード1件で検証しました。（レコード数が多いと文字数に関係なくFULLTEXT INDEXが早くなるため）
テストデータの準備
- テーブル作成
large_text_dataset_table
small_text_dataset_table
ストアドプロシージャ作成
DELIMITER //
# 文字数が多い場合
CREATE PROCEDURE large_text_dataset_single_test()
BEGIN
DECLARE sample_text VARCHAR(1000);
SET sample_text = CONCAT('サンプルテキスト ', 1, ' ', REPEAT('ランダム文字', 100)); -- 1000文字に調整
INSERT INTO large_text_dataset_table (content) VALUES (sample_text);
END //
# 文字数が少ない場合
CREATE PROCEDURE small_text_dataset_single_test()
BEGIN
DECLARE sample_text VARCHAR(10);
SET sample_text = CONCAT('サンプルテキスト ', 1); -- 10文字に調整
INSERT INTO small_text_dataset_table (content) VALUES (sample_text);
END //
DELIMITER ;
CALL large_text_dataset_single_test();
CALL small_text_dataset_single_test();
文字数の検証と結果
検証
SET profiling = 1;
- 文字数が多い場合
SELECT * FROM large_text_dataset_table WHERE MATCH(content) AGAINST('"サンプルテキスト"' IN BOOLEAN MODE);
SELECT * FROM large_text_dataset_table WHERE content LIKE '%サンプルテキスト%';
- 文字数が少ない場合
SELECT * FROM small_text_dataset_table WHERE MATCH(content) AGAINST('"サンプルテキスト"' IN BOOLEAN MODE);
SELECT * FROM small_text_dataset_table WHERE content LIKE '%サンプルテキスト%';
SHOW PROFILES;
結果
文字数が多いとFULLTEXT INDEXが速く、文字数が少ないとLIKE検索が速い
- 文字数1000文字ならFULLTEXT INDEXが速い
- FULLTEXT INDEX検索: 0.002秒
- LIKE検索: 0.004秒
| Query_ID | Duration | Query |
| 1025224 | 0.00218000 | SELECT * FROM large_text_dataset_table WHERE MATCH(content) AGAINST('"サンプルテキスト"' IN BOOLEAN MODE) |
| 1025225 | 0.00418100 | SELECT * FROM large_text_dataset_table WHERE content LIKE '%サンプルテキスト%' |
- 文字数10文字ならLIKE検索が速い
- FULLTEXT INDEX検索: 0.005秒
- LIKE検索: 0.001秒
| Query_ID | Duration | Query |
| 1025226 | 0.00540500 | SELECT * FROM small_text_dataset_table WHERE MATCH(content) AGAINST('"サンプルテキスト"' IN BOOLEAN MODE) |
| 1025227 | 0.00189100 | SELECT * FROM small_text_dataset_table WHERE content LIKE '%サンプルテキスト%' |
重複文字での比較
テストデータの準備
- テーブル作成
- high_duplication_dataset
- low_duplication_dataset
- ストアドプロシージャの作成
DELIMITER //
-- 重複文字が多い場合
CREATE PROCEDURE generate_high_repetition_dataset()
BEGIN
DECLARE sample_text TEXT;
SET sample_text = REPEAT('サンプルテキスト ', 10);
INSERT INTO high_duplication_dataset (content) VALUES (sample_text);
END //
-- 重複文字が少ない場合
CREATE PROCEDURE generate_low_repetition_dataset()
BEGIN
DECLARE sample_text TEXT;
SET sample_text = CONCAT('サンプルテキスト 1 ', REPEAT('ランダム文字', 8));
INSERT INTO low_duplication_dataset (content) VALUES (sample_text);
END //
DELIMITER ;
CALL generate_high_repetition_dataset();
CALL generate_low_repetition_dataset();
重複文字の検証と結果
検証
SET profiling = 1;
- 重複多い
SELECT * FROM high_duplication_dataset WHERE MATCH(content) AGAINST('"サンプルテキスト"' IN BOOLEAN MODE);
SELECT * FROM high_duplication_dataset WHERE content LIKE '%サンプルテキスト%';
- 重複少ない
SELECT * FROM low_duplication_dataset WHERE MATCH(content) AGAINST('"サンプルテキスト"' IN BOOLEAN MODE);
SELECT * FROM low_duplication_dataset WHERE content LIKE '%サンプルテキスト%'
SHOW PROFILES;
結果
重複が多いとFULLTEXT INDEXが速く、重複が少ないとLIKE検索が速い
- 重複が多いならFULLTEXT INDEXが速い
- FULLTEXT INDEX検索: 0.002秒
- LIKE検索: 0.009秒
| Query_ID | Duration | Query |
| 1 | 0.00241000 | SELECT * FROM high_duplication_dataset WHERE MATCH(content) AGAINST('"サンプルテキスト"' IN BOOLEAN MODE) |
| 2 | 0.00926900 | SELECT * FROM high_duplication_dataset WHERE content LIKE '%サンプルテキスト%' |
| Query_ID | Duration | Query |
| 3 | 0.00280200 | SELECT * FROM low_duplication_dataset WHERE MATCH(content) AGAINST('"サンプルテキスト"' IN BOOLEAN MODE) |
| 4 | 0.00187000 | SELECT * FROM low_duplication_dataset WHERE content LIKE '%サンプルテキスト%' |
まとめ
今回の検証では、レコード数が多い場合、文字数が多い場合、重複が多い場合の全てにおいて、FULLTEXT INDEXの検索速度がLIKE検索よりも優れていることが確認できました。しかし、どの検証も想定ほど大きな速度差が見られない結果となりました。
また、キャッシュの影響で速度が変動することや、テストデータの変更によって結果が異なることも確認できました。これらを考慮し実際の環境にてパフォーマンスを検証する際には、様々な条件下でテストを行うことが重要だと感じました。
