MySQLのLIKE ‘%日本語%’だと遅いので、日本語全文検索(FULLTEXT INDEXES)で部分検索をやってみる。laravel8でもやってみる！

admin

3年前

1, SQL文で試してみる

-- 検索したい文字列カラムに、フルテキストインデックスを貼る
-- textでもvarchar(255)でもOK！
ALTER TABLE テーブル名 ADD FULLTEXT INDEX ngram_idx (カラム名) WITH PARSER ngram;

-- 検索したい文字列カラムに、フルテキストインデックスを貼る

-- textでもvarchar(255)でもOK！

ALTER TABLE テーブル名 ADD FULLTEXT INDEX ngram_idx (カラム名) WITH PARSER ngram;

-- WHERE句が独特。IN BOOLEAN MODEは完全一致。
-- 何も指定しないと似たような単語でも引っかかる！
select *
from テーブル名
where MATCH(カラム名) AGAINST('日本語' IN BOOLEAN MODE)

-- WHERE句が独特。IN BOOLEAN MODEは完全一致。

-- 何も指定しないと似たような単語でも引っかかる！

select *

from テーブル名

where MATCH(カラム名) AGAINST('日本語' IN BOOLEAN MODE)

phpMyAdminで実行してみると、合計 52, クエリの実行時間： 0.0052 秒とか早そうだけど、表示まで時間かかる。
サーバ上でコマンドラインから実行してみたら、超早いのでインデックスの効果あり！
ネットワークとか描画の問題か？

1, 単語の頭にプラスをつけると検索ワード
2, スペースで区切って、単語の頭にプラスをつけると検索ワード(AND検索になる)
3, 単語の頭にマイナスをつけると除外ワード
4, 単語の頭に何も付けないとOR検索になる(複数ワードの時には、あんまり意味がない)

select *
from テーブル名
where MATCH(カラム名) AGAINST('+大阪 +ビル -東京' IN BOOLEAN MODE)

select *

from テーブル名

where MATCH(カラム名) AGAINST('+大阪 +ビル -東京' IN BOOLEAN MODE)

大阪とビルを含んで、東京を含まないレコードがSELECTされる

2, laravelのeloquentで試してみる。

migrationのテーブル定義に、部分検索したいカラムにフルテキスト・インデックスを設定しておく

public function up()
{
    Schema::create('users', function (Blueprint $table) {
        $table->id();
        $table->string('name');
        $table->string('email')->unique();
        $table->timestamp('email_verified_at')->nullable();
        $table->string('password');
        $table->rememberToken();
        $table->timestamps();
        $table->softDeletes();
    });

    // ALTER 文を実行しテーブルにコメントを設定
    DB::statement("ALTER TABLE users COMMENT 'ログイン・ユーザ情報'");

    // LIKE検索だと遅いので、ビル名にフルテキスト・インデックス追加
    DB::statement('ALTER TABLE usrs ADD FULLTEXT index ngram_idx (`name`) with parser ngram');
}

public function up()

{

Schema::create('users', function (Blueprint $table) {

$table->id();

$table->string('name');

$table->string('email')->unique();

$table->timestamp('email_verified_at')->nullable();

$table->string('password');

$table->rememberToken();

$table->timestamps();

$table->softDeletes();

});

// ALTER 文を実行しテーブルにコメントを設定

DB::statement("ALTER TABLE users COMMENT 'ログイン・ユーザ情報'");

// LIKE検索だと遅いので、ビル名にフルテキスト・インデックス追加

DB::statement('ALTER TABLE usrs ADD FULLTEXT index ngram_idx (`name`) with parser ngram');

}

UserController.phpで、実際の検索を記述。
MySQL依存なので、生SQLを記述しておく(将来的にはeloquentで書けたりするのだろうか？)

$keyword = $request->keyword;

$query = User::query();

// $query->where('name', 'like', "%$keyword%");
$query->whereRaw("match(`name`) against (? IN BOOLEAN MODE)", [$keyword]);

$users = $query->get();

$keyword = $request->keyword;

$query = User::query();

// $query->where('name', 'like', "%$keyword%");

$query->whereRaw("match(`name`) against (? IN BOOLEAN MODE)", [$keyword]);

$users = $query->get();

さすがにLIKEより全然速い！
これって、複数のカラムをフルテキスト・インデックス貼って大丈夫なのだろうか…。

関連記事