インデックスが効かないパターンと救済

インデックスがあっても効かないパターン

インデックスを作っても、条件の書き方によっては使われず全行走査（SCAN）のまま実行されます。

代表的なのは次の 3 つです。

• 列を関数や算術演算で包む条件（substr(col) / upper(col) / col + 0 など）
• 中間一致のLIKE '%x%'
• 否定条件（<> / NOT IN）

いずれもインデックスの並びと比較対象が対応しなくなることが原因です。

後半では、関数で包まざるを得ない場合の式インデックスと、特定の行だけを対象にする部分インデックスでの救済を扱います。

本記事では「インデックスの効かない書き方」と「インデックスの効く書き方」を計画で見比べます。

-- amount にインデックスがあっても
-- 列を関数で包むと全行走査に戻る例
DROP INDEX IF EXISTS ix_demo;
CREATE INDEX ix_demo ON perf_sales(amount);

-- 列を素のまま比較 → SEARCH
EXPLAIN QUERY PLAN
SELECT COUNT(*) FROM perf_sales WHERE amount >= 800000;

-- 列を関数で包む → SCAN に戻る
EXPLAIN QUERY PLAN
SELECT COUNT(*) FROM perf_sales WHERE abs(amount) >= 800000;

「2024 年の売上だけ件数を数えたい」という要件を考えます。sale_dateにインデックスを作ったうえで、列を関数で包む書き方と、範囲条件で素のまま比べる書き方の実行計画を見比べ、SCANとSEARCHがどう変わるかを観察します。インデックスはこのコンソール 1 回の実行で作り直し、計画を表示するところまでを自己完結させます。（正しく実行できれば解説が表示されます）

① DROP INDEX IF EXISTSでインデックスを消し、sale_dateの単一列インデックスを作成してください。

② sale_dateをsubstrで年だけ取り出して'2024'と比べるクエリの実行計画をEXPLAIN QUERY PLANで表示してください（列を関数で包む形）。

③ 続けて、同じ意味をsale_dateの範囲条件（2024 年の開始日以上・翌年の開始日未満）で表したクエリの実行計画を表示し、② と表示がどう変わるか見比べてください。

中間一致 LIKE

LIKEの前方一致（'P00%'のように先頭が固定される形）は、原則としてインデックスの並びで範囲を絞れる書き方です。

一方、中間一致（'%5%'）や後方一致（'%50'）は先頭が決まらないため、どのデータベースでもインデックスをたどれず全行走査になります。

ただしSQLite ではデフォルトで`LIKE`が大文字小文字を区別しないため、通常の BINARY 並びのインデックスとは噛み合わず、前方一致 LIKE でもSEARCHには使われずSCANになります（後述の callout 参照）。

そのため SQLite で前方一致をインデックス探索にしたいときは、product >= 'P00' AND product < 'P01'のように範囲条件で書き換えるのが素直な方法です。

中間一致 LIKE は、要件として必要なら全文検索の仕組みなど別の手段を検討します。

SQLite の LIKE はデフォルトで大文字小文字を区別しない

SQLite ではPRAGMA case_sensitive_likeがOFF（既定）のため、LIKEは大文字小文字を区別しない比較になります。

インデックスは通常の BINARY 並び（大文字小文字を区別する辞書順）で作られるため、LIKE 'P00%'のような前方一致でもインデックスの並びと比較ルールが噛み合わず、SEARCHには使われません。

このコンソールでLIKE 'P00%'を実行すると、計画はSCAN perf_sales USING COVERING INDEXになります。

回避策は、範囲条件で書き換える（product >= 'P00' AND product < 'P01'）、あるいは値側で大文字小文字の候補を列挙する（product IN ('P050', 'p050')）のが素直です。

本記事では一番素直な範囲条件への書き換えを採用します。

MySQL / PostgreSQL ではLIKEが大文字小文字を区別する設定であれば、前方一致は通常SEARCHになります。

-- product のインデックスで範囲条件と LIKE の計画を見比べる例
DROP INDEX IF EXISTS ix_demo;
CREATE INDEX ix_demo ON perf_sales(product);

-- 範囲条件 → 先頭が決まるので SEARCH
EXPLAIN QUERY PLAN
SELECT COUNT(*) FROM perf_sales
WHERE product >= 'P15' AND product < 'P16';

-- 前方一致 LIKE → SQLite では SCAN (大文字小文字を無視するため)
EXPLAIN QUERY PLAN
SELECT COUNT(*) FROM perf_sales WHERE product LIKE 'P15%';

-- 中間一致 LIKE → どの DB でも SCAN
EXPLAIN QUERY PLAN
SELECT COUNT(*) FROM perf_sales WHERE product LIKE '%9%';

「商品コードが特定の接頭辞で始まる売上を数えたい」という要件を、(A) 範囲条件、(B) 前方一致 LIKE、(C) 中間一致 LIKE の 3 つの書き方で計画を見比べます。productにインデックスを作り、SQLite でどの形がSEARCHになるかを観察します。

① DROP INDEX IF EXISTSでインデックスを消し、productの単一列インデックスを作成してください。

② WHERE product >= 'P00' AND product < 'P01'の範囲条件で件数を取り出すクエリの実行計画をEXPLAIN QUERY PLANで表示してください。

③ 同じ意味を前方一致 LIKE 'P00%'で書いたクエリの実行計画を表示し、② と表示がどう変わるか見比べてください。

④ さらに、中間一致 LIKE '%5%'のクエリの実行計画も表示してください。

カラムを upper() や算術演算で包むとインデックスが使われない

カラム（列）の絞り込みに関数や算術演算を当てると、たとえそのカラムにインデックスがあっても使えません。

インデックスはカラムの生の値で並んでいるため、upper(product)やamount + 10000のように加工した値を比較する形にすると、加工後の値とインデックスの並びが対応しないからです。

たとえばWHERE upper(product) = 'P050'は、すべての行でproductをupper()に通してから比較するため、productのインデックスは使われず全行走査になります。

同じくWHERE amount + 10000 = 900000は、すべての行でamount + 10000を計算してから比較するため、amountのインデックスは使われません。

直し方はカラム側を加工せず、値側で調整することです。

upper(product) = 'P050'は、想定される値が'P050'と'p050'の 2 通りならWHERE product IN ('P050', 'p050')に直せば、INがインデックスで複数値を一気に絞れます。

amount + 10000 = 900000は両辺から 10000 を引いてamount = 800000と直接書けば、amountのインデックスで一発で絞れます。

前記事のsubstr(sale_date, 1, 4)を範囲条件に直したのも、同じ「カラム側を加工しない」の応用です。

-- product のインデックスで列を lower() で包む例
DROP INDEX IF EXISTS ix_demo;
CREATE INDEX ix_demo ON perf_sales(product);

-- 列を lower() で包む → SCAN
EXPLAIN QUERY PLAN
SELECT COUNT(*) FROM perf_sales WHERE lower(product) = 'p001';

-- 値側に大文字小文字の候補を列挙 → SEARCH
EXPLAIN QUERY PLAN
SELECT COUNT(*) FROM perf_sales WHERE product IN ('P001', 'p001');

-- 算術演算で包む例 (qty のインデックスでの動き)
DROP INDEX IF EXISTS ix_qty;
CREATE INDEX ix_qty ON perf_sales(qty);

-- qty * 2 で包む → SCAN
EXPLAIN QUERY PLAN
SELECT COUNT(*) FROM perf_sales WHERE qty * 2 = 20;

-- 値側で式を整理 (両辺を 2 で割る) → SEARCH
EXPLAIN QUERY PLAN
SELECT COUNT(*) FROM perf_sales WHERE qty = 10;

「商品コード'P050'（または小文字表記の'p050'）の売上を数えたい」という要件を考えます。

productにインデックスを作り、列をupper()で包む書き方と、値側に候補値を列挙するIN (...)を使う書き方の実行計画を見比べます。

どちらも'P050'と'p050'の両方を対象にする比較なので結果は同じです。

① DROP INDEX IF EXISTSでインデックスを消し、productの単一列インデックスを作成してください。

② WHERE upper(product) = 'P050'のように列をupper()で包むクエリの実行計画をEXPLAIN QUERY PLANで表示してください。

③ 同じ意味をWHERE product IN ('P050', 'p050')で書いたクエリの実行計画を表示し、② と表示がどう変わるか見比べてください。

「金額がちょうど 800,000 の売上を数えたい」という要件で、amountにインデックスを作り、列に算術演算を当てる書き方と、値側で調整する書き方の実行計画を見比べます。

amount + 10000 = 900000は両辺から 10000 を引くとamount = 800000になり、数学的に完全に等価です。

① DROP INDEX IF EXISTSでインデックスを消し、amountの単一列インデックスを作成してください。

② WHERE amount + 10000 = 900000のように列に算術演算を当てるクエリの実行計画をEXPLAIN QUERY PLANで表示してください。

③ 同じ意味をWHERE amount = 800000で書いたクエリの実行計画を表示し、② と表示がどう変わるか見比べてください。

否定条件 (<>, NOT IN)

<>（等しくない）やNOT INのような否定条件は、対象行が「全体のほとんど」になることが多く、インデックスで絞る意味がありません。

たとえばstatusの値が'paid'（5 万行中 約 4 万行）/ 'pending'（約 5 千行）/ 'refunded'（約 5 千行）のとき、WHERE status <> 'paid'は 1 万行（全体の 20%）が対象です。

選択率がそれほど低くないため、全行走査の方がコストが安いと最適化器が判断します。

救済策は、等値条件で書き直すことです。

WHERE status <> 'paid'はWHERE status IN ('pending', 'refunded')に書き換えられ、こちらはインデックスで対象を絞れます。

-- status のインデックスに対し、NOT IN vs IN の書き換えを見比べる例
DROP INDEX IF EXISTS ix_demo;
CREATE INDEX ix_demo ON perf_sales(status);

-- 否定条件 (NOT IN) → 対象が広く SCAN
EXPLAIN QUERY PLAN
SELECT COUNT(*) FROM perf_sales WHERE status NOT IN ('refunded');

-- 等値 (IN) で書き換え → SEARCH
EXPLAIN QUERY PLAN
SELECT COUNT(*) FROM perf_sales WHERE status IN ('paid', 'pending');

「paid以外の売上を数えたい」という要件を考えます。statusにインデックスを作り、<>で書く場合とINで同じ意味を等値条件にする場合の実行計画を見比べます。

① DROP INDEX IF EXISTSでインデックスを消し、statusの単一列インデックスを作成してください。

② WHERE status <> 'paid'の件数を取り出すクエリの実行計画をEXPLAIN QUERY PLANで表示してください。

③ 同じ意味をWHERE status IN ('pending', 'refunded')で書き直したクエリの実行計画を表示し、② と表示がどう変わるか見比べてください。

式インデックスと部分インデックスで救済する

列を関数で包む形がどうしても必要なときは、式インデックス（列そのものではなく、式の結果に対して作るインデックス）で救えます。

CREATE INDEX ix ON perf_sales(substr(sale_date,1,4))のように、条件で使う式と同じ式でインデックスを作ると、その式での比較がインデックス探索になります。

条件側の式とインデックスの式が一致していることが条件です。

もう 1 つが部分インデックス（WHERE条件付きで、特定の行だけを対象に作るインデックス）です。

status='pending'の行が全体の一部（5 万行中 5 千行）なら、CREATE INDEX ix ON perf_sales(emp_id) WHERE status='pending'のように対象を絞ったインデックスを作れます。

インデックス自体が小さく済み、その条件を含むクエリで効率よく絞れます。

-- 式インデックス: 条件で使う式と同じ式に作る
DROP INDEX IF EXISTS ix_demo;
CREATE INDEX ix_demo ON perf_sales(substr(sale_date, 1, 4));

EXPLAIN QUERY PLAN
SELECT COUNT(*) FROM perf_sales
WHERE substr(sale_date, 1, 4) = '2024';

「処理中（pending）の売上を担当者で絞り込んで調べたい」という要件を考えます。pending は 5 万行中 5 千行と一部なので、その行だけを対象にした部分インデックスを作り、status='pending'を含むクエリの実行計画がインデックス探索になることを確かめます。

① DROP INDEX IF EXISTSでインデックスを消してください。

② emp_idに対して、statusが'pending'の行だけを対象にする部分インデックス（CREATE INDEX ... WHERE ...の形）を作成してください。

③ statusが'pending'かつ特定のemp_idの行を数えるクエリの実行計画をEXPLAIN QUERY PLANで表示し、部分インデックスが使われているか確認してください。