[MySQL]名前のLIKE検索が重すぎる件を調査した

背景

LIKE検索が重くてよくタイムアウトのエラーになるクエリがあったので、改善方法を考えました。
対象のクエリは以下です。
userの姓、名で検索する際のよくあるクエリです

SELECT users.id
FROM users
WHERE (users.last_name LIKE '%example%' OR users.first_name LIKE '%example%')
ORDER BY users.id
;

EXPLAIN結果

type：index
key：last_nameとfirst_nameの複合index
rows：テーブルの全件
Extra：Using where; Using index

INDEX

last_nameとfirst_nameの複合インデックスが貼られていた

原因

複雑なWHERE句(LIKE条件をOR句で繋げている)

やってみたこと

以下をやってみましたが、結果は変わらずでした。

• ORDER BY句削除
• 結合の削除
  • 元々のクエリはusersテーブルだけでなく６つほどテーブルを結合していたので、結合をなしにしてみました
• 検索文字から最初のワイルドカードを削除してみる
  • LIKE '%example%'をLIKE 'example%'に変更
• ORをCONCATに変更
  • WHERE CONCAT(users.last_name, users.first_name) LIKE '%example%';
• ORをUNIONに変更

  • SELECT users.id
    FROM users
    WHERE users.last_name LIKE '%example%'
    UNION
    SELECT users.id
    FROM users
    WHERE users.first_name LIKE '%example%';
    

ただ、以下をやったら少し変化が見えました

• last_name、first_nameそれぞれの単体のindexを作成

  • CREATE INDEX idx_last_name ON users(last_name);
    CREATE INDEX idx_first_name ON users(first_name);
    

元のクエリだと上記をやってもExpalain結果に変化はなかったのですが、検索ワードの頭のワイルドカードを削除したら、indexが効いてrowsの数がかなり減りました！
ただ頭にワイルドカードがついたままだと、変化はありませんでした><

結果

検索エンジンを使おう

高速で検索したい場合はElasticsearchやOpenSearchなどの検索エンジンを導入するのが良さそうです

補足

MySQLの全文検索

FULLTEXT型インデックスを追加することでも改善できました。

-- FULLTEXT型のindexを作成
ALTER TABLE users ADD FULLTEXT (last_name, first_name);

SELECT users.id
FROM users
WHERE MATCH(last_name, first_name) AGAINST('example' IN BOOLEAN MODE);

ただしデフォルトでは３文字以上の検索ワードしか使えないみたいで、２文字だと検索が０件になりました...。
設定ファイルをいじればなんとかなるみたい？(今回は試してないです)
メリットデメリットについても調べきれてないないので、導入する場合は調査が必要です。(公式ドキュメント)

感想

特になし。無。