DIVX テックブログ

catch-img

FULLTEXT INDEX vs LIKE検索の速度比較


目次[非表示]

  1. 1.FULLTEXT INDEXとは
  2. 2.LIKE検索とは
  3. 3.何を検証するのか
  4. 4.検証開始
  5. 5.レコード数での比較
    1. 5.1.テストデータの準備
  6. 6.レコード数の検証と結果
    1. 6.1.検証
    2. 6.2.結果
  7. 7.文字数での比較
    1. 7.1.テストデータの準備
  8. 8.文字数の検証と結果
    1. 8.1.検証
    2. 8.2.結果
  9. 9.重複文字での比較
    1. 9.1.テストデータの準備
  10. 10.重複文字の検証と結果
    1. 10.1.検証
    2. 10.2.結果
  11. 11.まとめ


FULLTEXT INDEXとは

MySQL や MariaDB などのデータベースで提供される全文検索のこと

値が一部一致している結果を取得できる

  • メリット
    • 大規模なデータから特定の単語を効率的に探すときに有効
  • デメリット
    • LIKE検索に比べると設定が複雑

LIKE検索とは

SQLで使用できる部分一致検索のこと

  • メリット
    • 設定が簡単で柔軟に部分一致検索ができる
  • デメリット
    • 大規模なデータではパフォーマンスが低下しやすい

何を検証するのか

FULLTEXT INDEX検索とLIKE検索を実施し、検索速度にどのくらい違いが出るかを以下の観点で検証します。

  • レコード数の速度比較
    • 大規模データ(レコード100万件)と小規模データ(レコード100件)で検索速度を比較
  • 文字数の速度比較
    • 各レコードの文字数が多い場合(1000文字)と少ない場合(10文字)で検索速度を比較
  • 重複文字の速度比較

    • データの文字列の重複が多い場合と少ない場合で検索速度を比較

検証開始

テストデータの作成にはストアドプロシージャを使用しました。
以下の手順でテストデータ作成から検証まで行っています。

  • データ挿入用のテーブル作成
  • ストアドプロシージャでテストデータ作成のSQL記述
  • データを生成
  • パフォーマンス比較
    • FULLTEXT INDEXとLIKE検索を実施し、速度比較

レコード数での比較

テストデータの準備

  • データベースを作成し切り替える
- `CREATE DATABASE IF NOT EXISTS TestDB;`
- `USE TestDB;`
  • テーブルを2つ作成

- レコード数が多い場合
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 ;
  • レコード1件にデータ生成
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 '%サンプルテキスト%'                                    |
  • 重複が少ないならLIKE検索が速い
    FULLTEXT INDEX検索: 0.002秒
    LIKE検索: 0.001秒

| 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検索よりも優れていることが確認できました。しかし、どの検証も想定ほど大きな速度差が見られない結果となりました。
また、キャッシュの影響で速度が変動することや、テストデータの変更によって結果が異なることも確認できました。これらを考慮し実際の環境にてパフォーマンスを検証する際には、様々な条件下でテストを行うことが重要だと感じました。


  ご相談フォーム | 株式会社divx(ディブエックス) DIVXのご相談フォームページです。 株式会社divx(ディブエックス)


お気軽にご相談ください


ご不明な点はお気軽に
お問い合わせください

サービス資料や
お役立ち資料はこちら

DIVXブログ

テックブログ タグ一覧

採用ブログ タグ一覧

人気記事ランキング

GoTopイメージ