はじめに

エキサイトで内定者インターンをしている岡崎です。今回は業務で全文検索を試してみたので、そこで学んだ話をさせていただきます。

経緯

今回やりたかったことは、部分一致で取得することでした。例を下記に記します。

テーブル定義の例として、本と、それに紐づく本のタグのテーブルがあることとします。

CREATE TABLE `book` (
  `id` int(11) NOT NULL COMMENT 'ID',
  `title` text CHARACTER SET utf8mb4 COMMENT 'タイトル',
  `summary` text CHARACTER SET utf8mb4 COMMENT 'サマリー',
  `created_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '登録時間',
  `updated_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新時間'
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE `book_tag` (
  `id` int(11) NOT NULL COMMENT 'ID',
  `book_id` int(11) NOT NULL COMMENT 'ブックID',
  `name` text CHARACTER SET utf8mb4 COMMENT 'タグ名',
  `created_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '登録時間',
  `updated_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新時間'
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

また、SQLの例としては以下です。 JOINしたテーブルの部分一致検索をすることとします。

SELECT 
    *
FROM
    book
INNER JOIN book_tag
    ON book.id = book_tag.book_id
WHERE
    book.title like '%文豪%'
    OR book.summary like '%文豪%'
    OR book_tag.name like '%文豪%'
;

しかし、上記のようなSQLでは効率が悪い場合があります。実際、私のやっていたところではtypeにALLがあったり、filesortとなっていました・・・。これを解決するため、全文検索を使ってみよう！　ということになりました。

全文検索

MySQLでは、以下のような形でFULLTEXT INDEXを貼ることができます。

# 一つのカラム
CREATE FULLTEXT INDEX `index` ON `book` (`title`) WITH PARSER ngram;

# 複数のカラム
CREATE FULLTEXT INDEX `index` ON `book` (`title`, `summary`) WITH PARSER ngram;

全文検索をするには、MATCH(title) AGAINST('文豪')、MATCH(title, summary) AGAINST('文豪')のように、MATCH(col, col2・・・) AGAINST (キーワード)のように書きます。ちなみに、MATCHの()の中には、FULLTEXT INDEXを貼るときに指定したカラムを全て書くこととなります。

なので、先程かいたSQLを全文検索で書いてみると、以下のようになります。

SELECT 
    *
FROM
    book
INNER JOIN book_tag
    ON book.id = book_tag.book_id
WHERE
    MATCH(`book.title`,`book.summary`) AGAINST('文豪')
    OR MATCH(`book_tag.name`) AGAINST('文豪')
;