MySQLのインデックス設定において、「カーディナリティーの低い順」というルールを無視してもいいでしょうか

Question

質問の概要

MySQLでインデックスの設定方法について、「カーディナリティーの低い順」というルールを無視すべきかどうか悩んでいます。アドバイス頂けませんでしょうか。

現状のテーブル

現状「公式の、日本語の、人気順」等の取得があります。
そのために以下postsテーブルでは、最後の行のindex_popular_postsのインデックスを設定しました。

公式アカウントも一般ユーザーも多様な言語で投稿できるのがpostsテーブルですが、そのうち「公式の、日本語の、人気順」とか「公式の、英語の、不人気順」等で取得したいためです。

CREATE TABLE posts (
	 ID                   BIGINT(20)    UNSIGNED NOT NULL AUTO_INCREMENT
	,content              VARCHAR(1000) CHARACTER SET utf8mb4 NOT NULL
	,author_ID            BIGINT(20)    UNSIGNED NOT NULL DEFAULT 0
	,is_trash             TINYINT(1)    UNSIGNED NOT NULL DEFAULT 0
			
	#「公式の、日本語の、人気順」等の取得用
	,is_offisial          TINYINT(1)    UNSIGNED NOT NULL DEFAULT 0
	,lang_id              INT(3)        UNSIGNED NOT NULL
	,count_iine           INT(6)        UNSIGNED NOT NULL DEFAULT 0
			
	# 作成情報
	,created_at           DATETIME(6)   NOT NULL DEFAULT CURRENT_TIMESTAMP(6)
	,updated_at           DATETIME(6)   NOT NULL DEFAULT CURRENT_TIMESTAMP(6) ON UPDATE CURRENT_TIMESTAMP(6)
		
	# 設定
	,PRIMARY KEY  (ID)
	,INDEX index_popular_posts(is_offisial,lang_id,count_iine) #「公式の、日本語の、人気順」等の取得用
);

発生した問題

このインデックスindex_popular_postsは「カーディナリティーの低い順」というルールに準拠しています。
is_officialは0|1のいずれかが入り、lang_idは10種類の言語が入り、count_iineにはいいねされた数が入るためです。

しかし今回、「公式の、日本語の、人気順」に限らず、「日本語の、人気順」等の取得が生じましたので質問です。

これはインデックスindex_popular_postsのうち、最初に設定されている「公式の（is_offisial）」が抜けた取得となりますので、インデックスが効かないという問題が生じると思います。

考えられる対処法

ここで対処法として、以下①②を考えてみました。

－－－－－－－－－－
①「カーディナリティーの低い順」というルールを無視する
index_popular_posts(lang_id,is_offisial,count_iine)というインデックスで設定する

②インデックスを増やす
現状のindex_popular_posts(is_offisial,lang_id,count_iine)だけでなく、
さらにindex_popular_posts2(lang_id,count_iine)でも設定する
－－－－－－－－－－

以上の2つうち、どれがなぜ良いのか？または別の良い対処法があるのか？を知りたいです。

使用環境

select version() -> 5.7.31

Answer 1

②ですね。

別な方法としては、インデックスは現状のまま、条件を「ANY、日本語の、人気順」にする事です。
条件の左辺と右辺を等価に(is_offisial=is_offisial)すればANYになります。

SQLでパラメータを含む条件式としては、公式のパラメータを指定しない場合はNullとして、
is_offisial=coalesce(パラメータ, is_offisial)
のようにすれば良いかと思います。

追記

インデックスを適用するにあたっては以下のポイントがあります。
A.使用されるインデックスにする事
B.効率の良いインデックスにする事
C.共用されるインデックスにする事（※無駄なインデックスを作らない事）
※検索性能の観点からは、優先順位はA>B>Cです。更新やリソースの観点からはCが優先です。

現状のままであれば、AまたはBに該当しません。
①では少なくともBではなく、Cを目的としていますね。
②はAおよびBを満たし、Cに該当するかどうかという所です。

提示した案は、AおよびCを満たし、Bは完全ではありません。

性能の観点からは②がお薦めであり、提示した案は①の亜種です。

Comments

[nikuatsu]

ご回答ありがとうございます。
知らぬテクだらけで大変参考になりました。

もしよろしければなぜ①より②がお好みなのか、ご教示いただけませんでしょうか。

[nikuatsu]

改めて考えてみたのですが、こういう対処法はどうでしょうか？

カラム `count_iine`は、is_official=1 の場合だけ ＋100 しておく

これによってインデックスは
index_popular_posts(lang_id,count_iine)
だけで、「公式の、日本語の、人気順」にも「日本語の、人気順」にも対処できるか？と考えました。

つまり `count_iine` が 100 以上の場合だけ公式だと解釈してSELECTするわけです。（実際には100ではなく、最大のいいね数を上回る値ですが。）

[nikuatsu]

失礼しました。上記の対処法では「日本語の、人気順」の取得において、is_official=1 のレコードだけ -100 しておいて、その上でSELECTする。という冗長な処理が必要となってしまい本末転倒でした。