MySQL で複数のインデックスを明示的に使い分けたい

例えば次のようなテーブルとインデックスがあったとき：

CREATE TABLE `CatalystStatus` (
    `id` varchar(191) NOT NULL,
    `body` text NOT NULL,
    `userId` varchar(191) NOT NULL,
    `createdAt` datetime(3) NOT NULL DEFAULT current_timestamp(3),
    `updatedAt` datetime(3) NOT NULL DEFAULT current_timestamp(3),
    PRIMARY KEY (`id`),
    KEY `CatalystStatus_userId_idx` (`userId`),
    FULLTEXT KEY `CatalystStatus_body_idx` (`body`),
    FULLTEXT KEY `CatalystStatus_ngram_idx` (`body`) WITH PARSER ngram
) ENGINE InnoDB,
  CHARSET utf8mb4,
  COLLATE utf8mb4_unicode_ci;

カラム body に対して、通常の FULLTEXT インデックスである CatalystStatus_body_idx と、 N-gram で日本語での検索もるようにした CatalystStatus_ngram_idx の2つがあるとします。 この状態でインデックスを指定 (明示) せずに全文検索を行う場合を考えて、テーブル上に下記の3つのデータがあった際、

• #めいゆん #めいゆん3D
• はっぴーめいゆん
• めいゆんちゃんと日帰りデート！

結果としては

SELECT body FROM CatalystStatus WHERE MATCH(body) AGAINST ("めいゆん" IN BOOLEAN MODE) ORDER BY createdAt DESC;
#めいゆん #めいゆん3D


EXPLAIN SELECT body FROM CatalystStatus WHERE MATCH(body) AGAINST ("めいゆん" IN BOOLEAN MODE) ORDER BY createdAt DESC;
| id  | select_type | table          | partitions | type     | possible_keys           | key                     | key_len | ref   | rows | filtered | Extra                                             |
| --- | ----------- | -------------- | ---------- | -------- | ----------------------- | ----------------------- | ------- | ----- | ---- | -------- | ------------------------------------------------- |
| 1   | SIMPLE      | CatalystStatus | NULL       | fulltext | CatalystStatus_body_idx | CatalystStatus_body_idx | 0       | const | 1    | 100      | Using where; Ft_hints: no_ranking; Using filesort |

のように、ハッシュタグなどで区切られたもののみが引っかかります。 ただ、これだと本来引っかかって欲しい日本語の文章中に含まれるものは得られないので、インデックスを指定することで N-gram を用いた結果を得ることが可能です。

SELECT body FROM CatalystStatus USE INDEX (ngram_idx) WHERE MATCH(body) AGAINST ("めいゆん" IN BOOLEAN MODE) ORDER BY createdAt DESC;
#めいゆん #めいゆん3D
はっぴーめいゆん
めいゆんちゃんと日帰りデート！

EXPLAIN SELECT body FROM CatalystStatus USE INDEX (ngram_idx) WHERE MATCH(body) AGAINST ("めいゆん" IN BOOLEAN MODE) ORDER BY createdAt DESC;
| id  | select_type | table          | partitions | type     | possible_keys | key       | key_len | ref   | rows | filtered | Extra                                             |
| --- | ----------- | -------------- | ---------- | -------- | ------------- | --------- | ------- | ----- | ---- | -------- | ------------------------------------------------- |
| 1   | SIMPLE      | CatalystStatus | NULL       | fulltext | ngram_idx     | ngram_idx | 0       | const | 1    | 100      | Using where; Ft_hints: no_ranking; Using filesort |

キーワードとしては USE INDEX (xxx) の部分で、これを FROM 句の後に追加することで指定したインデックスの使用を強制することができます。 ということでメモでした。