フルテキストインデックスで ORDER BY が遅い

Question

前提・実現したいこと

フルテキストインデックスでの検索において、ORDER BYを早めたいです。

発生している問題

下記のCREATEに対するSELECTにおいて、ORDER BYがあると約10秒、ないと0.1秒となります。

該当のソースコード

テスト用レコードでmy_contentsには100万件ほどが、my_tag_relationsには1000万件ほどがINSERTされています。

CREATE TABLE IF NOT EXISTS my_contents (
     ID                   BIGINT(20)    UNSIGNED NOT NULL AUTO_INCREMENT
    ,sentences_ID         BIGINT(20)    UNSIGNED NOT NULL
    ,is_official          TINYINT(1)    UNSIGNED NOT NULL DEFAULT 0
    ,lang_id              INT(3)        UNSIGNED NOT NULL DEFAULT 0 
    ,count_likes          INT(10)       UNSIGNED NOT NULL DEFAULT 0

    ,PRIMARY KEY  (ID)
    ,INDEX idx_my_contents_01 (is_official, lang_id, count_likes)
    ,INDEX idx_my_contents_02 (sentences_ID)
    ,INDEX idx_my_contents_03 (count_likes, ID) 
);

CREATE TABLE IF NOT EXISTS my_sentences (
     ID                   BIGINT(20)    UNSIGNED NOT NULL AUTO_INCREMENT
    ,sentence             VARCHAR(500)  CHARACTER SET utf8mb4 NOT NULL

    ,PRIMARY KEY  (ID)
    ,UNIQUE u_my_sentences_01 (sentence)
    ,FULLTEXT full_my_sentences_01 (sentence) WITH PARSER ngram
);

CREATE TABLE IF NOT EXISTS my_tags (
     ID                   BIGINT(20)    UNSIGNED NOT NULL AUTO_INCREMENT
    ,tag_kind_id          INT(3)        UNSIGNED NOT NULL DEFAULT 1 
    ,tag_name             VARCHAR(500)  NOT NULL
    ,lang_id              INT(3)        UNSIGNED NOT NULL DEFAULT 0 
    ,is_official          TINYINT(1)    UNSIGNED NOT NULL DEFAULT 0

    ,PRIMARY KEY  (ID)
    ,INDEX u_my_tags_01 (tag_kind_id, tag_name)
    ,INDEX idx_my_tags_01 (is_official, tag_kind_id, lang_id) 
    ,INDEX idx_my_tags_02 (tag_name)
);

CREATE TABLE IF NOT EXISTS my_tag_relations (
     tags_ID              BIGINT(20)    UNSIGNED NOT NULL
    ,contents_ID          BIGINT(20)    UNSIGNED NOT NULL

    ,PRIMARY KEY  (tags_ID, contents_ID)
    ,INDEX idx_my_tag_relations_01 (contents_ID)    
    ,CONSTRAINT fk_my_tag_relations_01 FOREIGN KEY (tags_ID) REFERENCES my_tags(ID)
    ,CONSTRAINT fk_my_tag_relations_02 FOREIGN KEY (contents_ID) REFERENCES my_contents(ID)
);

上記に対し次のSELECTをしましたが、10秒ほどかかってしまいます。

SELECT  contents.ID

FROM    my_contents contents
        LEFT JOIN my_sentences sentences
                  ON sentences.ID = contents.sentences_ID
        LEFT JOIN my_tag_relations tr
                  ON tr.contents_ID = contents.ID
        LEFT JOIN my_tags tags
                  ON tags.ID = tr.tags_ID

-- 結果は「100 total, Query took 9.2875 seconds. 」と10秒ほどかかってしまいます
WHERE  MATCH (sentences.sentence) AGAINST ('あいうえお' IN BOOLEAN MODE) OR
       tags.tag_name = 'あいうえお'

GROUP BY contents.ID        
ORDER BY contents.count_likes DESC, contents.ID DESC
LIMIT 0, 100

試したこと

遅い原因を突き止めるべく上記SELECTでEXPLAINを出しましたが、typeにAllがあるわけでもなく、これでいいように見えます。

続いてORDER BYを削除した次のSELECTでは0.1秒ほどになりました。

-- 結果は「100 total, Query took 0.1099 seconds.」と0.1秒ほどになりました。
WHERE  MATCH (sentences.sentence) AGAINST ('あいうえお' IN BOOLEAN MODE) OR
       tags.tag_name = 'あいうえお'

GROUP BY contents.ID        
# ORDER BY contents.count_likes DESC, contents.ID DESC
LIMIT 0, 100

しかしORDER BYを削除したくはありませんので、残したままで取得を早める方法を探しています。
良い方法ございましたら宜しくお願い致します。

補足情報（FW/ツールのバージョンなど）

MySQLは 5.7 系を使用しています。

Comments

[yambejp]

LEFT JOINで組む必要はあるのでしょうか？

[nikuatsu]

LEFT JOINせずに上記のようなSELECTが実現できるということですか？
例えばどのようになるかご教示頂けませんでしょうか。

[yambejp]

my_contents に対するその他テーブルのリレーションの状況がわからないのでなんとも言えません。
そもそも正規化が必要な状況かどうかも判断できないので

[nikuatsu]

そうでしたか。ありがとうございます。

Answer 1

そもそも、以下の認識が正しくありません。

GROUP BY contents.ID        
ORDER BY contents.count_likes DESC, contents.ID DESC

group by していない、contents.count_likesをoder by項目に指定しているからです。

Comments

[nikuatsu]

どうもありがとうございます。
ORDER BY contents.count_likes DESC, contents.ID DESC
に対しては
GROUP BY contents.count_likes, contents.ID
のように、互いに同じ順序で同じカラムを指定する。
という認識でよろしいでしょうか？

[sazi]

それで大丈夫だと思います。

[nikuatsu]

ありがとうございます。ORDER BY の部分の認識は必須だったようで助かりました。
しかし 'あいうえお' のように存在する値のときはすぐに取得できましたが、'ABCDE' のように存在しない値のときにはやはり10秒ほどかかってしまいました。ORDER BY の部分以外にも原因があるようで、もしお心当たりございましたら宜しくお願い致します。

[sazi]

存在しない語句の場合、FULLTEXTインデックスの全量に対して検索が行われているという事になります。
その場合の実行計画を確認してみてください。