注文日で絞り込むときに WHERE DATE(ordered_at) = ‘2026-10-05’ と書くと、ordered_at 列にインデックスがあっても使われません。列に関数をかけると、インデックスに入っている値とそのまま比べられなくなるためです。MySQL では、式の結果を列として持つ生成列（generated column）を作るか、式そのものにインデックスを貼る関数インデックスを使うと、この書き方のままでインデックスが効きます。生成列の VIRTUAL と STORED の違い、JSON の値に関数インデックスを貼る方法、照合順序が合わずにインデックスが使われない落とし穴を、EXPLAIN の結果つきでまとめました。
以下の結果は MySQL 8.0 で実際に実行したものです。
注文テーブルで考える
ネットショップの注文テーブルを例にします。単価（price）と個数（qty）、注文日時（ordered_at）があり、注文の経路（web・app・store）は JSON の detail 列に入っています。インデックスの効き方を見るため、再帰 CTE で1000件の注文を入れておきます。store の注文は、1000件のうち20件だけです。
CREATE TABLE orders (
id INT AUTO_INCREMENT PRIMARY KEY,
price INT NOT NULL,
qty INT NOT NULL,
ordered_at DATETIME NOT NULL,
detail JSON NOT NULL
);
INSERT INTO orders (price, qty, ordered_at, detail)
WITH RECURSIVE seq (n) AS (
SELECT 1 UNION ALL SELECT n + 1 FROM seq WHERE n < 1000
)
SELECT
100 * (n % 30 + 1),
n % 5 + 1,
TIMESTAMP '2026-01-01 09:00:00' + INTERVAL n * 7 HOUR,
JSON_OBJECT('channel', IF(n % 50 = 0, 'store', IF(n % 2 = 0, 'web', 'app')))
FROM seq;
SELECT * FROM orders WHERE id <= 3;+----+-------+-----+---------------------+--------------------+
| id | price | qty | ordered_at | detail |
+----+-------+-----+---------------------+--------------------+
| 1 | 200 | 2 | 2026-01-01 16:00:00 | {"channel": "app"} |
| 2 | 300 | 3 | 2026-01-01 23:00:00 | {"channel": "web"} |
| 3 | 400 | 4 | 2026-01-02 06:00:00 | {"channel": "app"} |
+----+-------+-----+---------------------+--------------------+生成列の基本（VIRTUAL と STORED）
生成列は、ほかの列から計算した値を持つ列です。列の定義に AS (式) と書くと、値は MySQL が式から決めます。金額（price * qty）と注文日（DATE(ordered_at)）を、生成列として足してみます。
ALTER TABLE orders
ADD COLUMN amount INT AS (price * qty) STORED,
ADD COLUMN ordered_date DATE AS (DATE(ordered_at)) VIRTUAL;
SELECT id, price, qty, amount, ordered_at, ordered_date FROM orders WHERE id <= 3;+----+-------+-----+--------+---------------------+--------------+
| id | price | qty | amount | ordered_at | ordered_date |
+----+-------+-----+--------+---------------------+--------------+
| 1 | 200 | 2 | 400 | 2026-01-01 16:00:00 | 2026-01-01 |
| 2 | 300 | 3 | 900 | 2026-01-01 23:00:00 | 2026-01-01 |
| 3 | 400 | 4 | 1600 | 2026-01-02 06:00:00 | 2026-01-02 |
+----+-------+-----+--------+---------------------+--------------+VIRTUAL と STORED の違いは、計算した値をディスクに書いておくかどうかです。
| 項目 | VIRTUAL | STORED |
|---|---|---|
| 値を計算するとき | 行を読むとき | INSERT・UPDATE のとき |
| ディスクの容量 | 使わない | 使う |
| どちらも書かなかったとき | こちらになる | – |
| インデックス | 貼れる（InnoDB） | 貼れる |
| 外部キーの参照先 | なれない | なれる |
| 既存のテーブルに足すとき | テーブルを書き直さない | テーブル全体を書き直す |
迷ったら VIRTUAL で始めて、計算が重い式や外部キーに使いたい列だけ STORED にします。どちらで作られたかは、information_schema.COLUMNS で確かめられます。
SELECT COLUMN_NAME, EXTRA, GENERATION_EXPRESSION
FROM information_schema.COLUMNS
WHERE TABLE_SCHEMA = 'demo' AND TABLE_NAME = 'orders' AND EXTRA LIKE '%GENERATED%'
ORDER BY ORDINAL_POSITION;+--------------+-------------------+----------------------------+
| COLUMN_NAME | EXTRA | GENERATION_EXPRESSION |
+--------------+-------------------+----------------------------+
| amount | STORED GENERATED | (`price` * `qty`) |
| ordered_date | VIRTUAL GENERATED | cast(`ordered_at` as date) |
+--------------+-------------------+----------------------------+DATE(ordered_at) が cast(`ordered_at` as date) に書きかわっています。MySQL が式を内部の形に直して保存するためで、結果は変わりません。
元の列を更新すると、生成列の値も一緒に変わります。生成列に自分で値を入れようとすると、エラーになります。
UPDATE orders SET qty = 10 WHERE id = 1;
SELECT id, price, qty, amount FROM orders WHERE id = 1;+----+-------+-----+--------+
| id | price | qty | amount |
+----+-------+-----+--------+
| 1 | 200 | 10 | 2000 |
+----+-------+-----+--------+INSERT INTO orders (price, qty, ordered_at, detail, amount)
VALUES (500, 2, '2026-11-01 10:00:00', '{"channel": "web"}', 1000);ERROR 3105 (HY000): The value specified for generated column 'amount' in table 'orders' is not allowed.生成列に書ける値は DEFAULT だけです。INSERT の列の一覧には、生成列を入れないようにします。
生成列にインデックスを貼って DATE() のまま速くする
生成列にインデックスを貼ると、WHERE の式が生成列の定義と同じときに、オプティマイザがそのインデックスを使います。クエリの側で ordered_date と書く必要はありません。まずはインデックスがない状態の EXPLAIN です（主な列だけ抜き出しています）。
EXPLAIN SELECT id, ordered_at FROM orders WHERE DATE(ordered_at) = '2026-10-05';+------+---------------+------+------+-------------+
| type | possible_keys | key | rows | Extra |
+------+---------------+------+------+-------------+
| ALL | NULL | NULL | 1000 | Using where |
+------+---------------+------+------+-------------+ordered_date にインデックスを貼って、同じクエリをもう一度見ます。
CREATE INDEX idx_ordered_date ON orders (ordered_date);
EXPLAIN SELECT id, ordered_at FROM orders WHERE DATE(ordered_at) = '2026-10-05';+------+------------------+------------------+------+-------+
| type | possible_keys | key | rows | Extra |
+------+------------------+------------------+------+-------+
| ref | idx_ordered_date | idx_ordered_date | 3 | NULL |
+------+------------------+------------------+------+-------+type が ALL（全件を読む）から ref（インデックスで探す）に変わり、読む行の見積もり（rows）は1000から3に減りました。実際の結果も3件です。
SELECT id, ordered_at FROM orders WHERE DATE(ordered_at) = '2026-10-05';+-----+---------------------+
| id | ordered_at |
+-----+---------------------+
| 949 | 2026-10-05 04:00:00 |
| 950 | 2026-10-05 11:00:00 |
| 951 | 2026-10-05 18:00:00 |
+-----+---------------------+アプリのクエリを書きかえられないときでも、テーブルに生成列とインデックスを足すだけで速くできます。EXPLAIN の各列の意味はEXPLAIN の読み方の記事にまとめています。
関数インデックスで JSON の値を検索する
MySQL 8.0.13 からは、列を足さずに式へ直接インデックスを貼れます。これが関数インデックス（functional key part）です。CREATE INDEX の中で、式を二重のかっこで囲みます。JSON の detail 列から経路を取り出す式にそのまま貼ろうとすると、エラーになります。
CREATE INDEX idx_channel ON orders ((detail->>'$.channel'));ERROR 3757 (HY000): Cannot create a functional index on an expression that returns a BLOB or TEXT. Please consider using CAST.->> 演算子の結果は LONGTEXT 型です。長さの決まっていない文字列には、インデックスを貼れません。エラー文のとおり CAST で長さを決め、あわせて COLLATE utf8mb4_bin も付けます。COLLATE が要る理由は、次の落とし穴の節で説明します。
CREATE INDEX idx_channel ON orders ((CAST(detail->>'$.channel' AS CHAR(10)) COLLATE utf8mb4_bin));
EXPLAIN SELECT COUNT(*) FROM orders WHERE detail->>'$.channel' = 'store';
SELECT COUNT(*) FROM orders WHERE detail->>'$.channel' = 'store';+------+---------------+-------------+------+-------+
| type | possible_keys | key | rows | Extra |
+------+---------------+-------------+------+-------+
| ref | idx_channel | idx_channel | 20 | NULL |
+------+---------------+-------------+------+-------+
+----------+
| COUNT(*) |
+----------+
| 20 |
+----------+クエリは detail->>’$.channel’ = ‘store’ のままで、idx_channel が使われました。読む行も store の20件だけです。
関数インデックスは、MySQL の中では見えない VIRTUAL の生成列として作られます。生成列を自分で作る方法との違いを並べておきます。
| 項目 | 生成列＋インデックス | 関数インデックス |
|---|---|---|
| 使えるバージョン | MySQL 5.7 から | MySQL 8.0.13 から |
| 計算した値を SELECT で見る | 列名で見られる | 式をもう一度書く |
| SELECT * の結果 | 列が増える | 変わらない |
| 主キーに使う | STORED なら使える | 使えない |
計算した値を画面や集計でも使うなら生成列、検索を速くしたいだけなら関数インデックスが手軽です。JSON 型の基本はJSON 型の記事で扱っています。
よくある落とし穴
式が少しでも違うとインデックスは使われない
オプティマイザがインデックスを使うのは、WHERE の式がインデックスの式と同じときだけです。同じ日付を DATE_FORMAT で比べると、結果は同じでも全件を読みます。
EXPLAIN SELECT id, ordered_at FROM orders WHERE DATE_FORMAT(ordered_at, '%Y-%m-%d') = '2026-10-05';+------+---------------+------+------+-------------+
| type | possible_keys | key | rows | Extra |
+------+---------------+------+------+-------------+
| ALL | NULL | NULL | 1000 | Using where |
+------+---------------+------+------+-------------+チームで書き方をそろえておくか、よく使う書き方に合わせてインデックスを作ります。インデックスが使われないほかの書き方は、インデックスが効かないパターンの記事にあります。
COLLATE を付けないと JSON の検索でインデックスが使われない
CAST(… AS CHAR) の結果の照合順序は utf8mb4_0900_ai_ci で、->> の結果は utf8mb4_bin です。照合順序が違うと、オプティマイザは WHERE の式とインデックスの式を同じものと見なしません。COLLATE を付けずに作り直すと、さきほどのクエリは全件を読むようになります。
DROP INDEX idx_channel ON orders;
CREATE INDEX idx_channel_ci ON orders ((CAST(detail->>'$.channel' AS CHAR(10))));
EXPLAIN SELECT COUNT(*) FROM orders WHERE detail->>'$.channel' = 'store';+------+---------------+------+------+-------------+
| type | possible_keys | key | rows | Extra |
+------+---------------+------+------+-------------+
| ALL | NULL | NULL | 1000 | Using where |
+------+---------------+------+------+-------------+クエリにも CAST まで書けば、このインデックスが使われます。
EXPLAIN SELECT COUNT(*) FROM orders WHERE CAST(detail->>'$.channel' AS CHAR(10)) = 'store';+------+----------------+----------------+------+-------+
| type | possible_keys | key | rows | Extra |
+------+----------------+----------------+------+-------+
| ref | idx_channel_ci | idx_channel_ci | 20 | NULL |
+------+----------------+----------------+------+-------+utf8mb4_bin は大文字と小文字を区別し、utf8mb4_0900_ai_ci は区別しません。’Store’ でも見つけたいなら COLLATE を付けずに作り、クエリにも CAST を書きます。区別してよいなら COLLATE utf8mb4_bin を付けて、クエリは ->> のままにします。
NOW() など、実行のたびに値が変わる関数は使えない
注文から何日たったかを生成列にしようとすると、エラーになります。
ALTER TABLE orders ADD COLUMN days_ago INT AS (DATEDIFF(NOW(), ordered_at));ERROR 3763 (HY000): Expression of generated column 'days_ago' contains a disallowed function: now.生成列に使えるのは、同じ入力なら必ず同じ結果を返す関数だけです。NOW()・CURDATE()・RAND() のほか、サブクエリや変数、ストアドファンクションも使えません。今日を基準にした値は、SELECT のときに計算します。
PostgreSQL では GENERATED ALWAYS AS (式) STORED で生成列を作ります。PostgreSQL 17 までは STORED だけで、VIRTUAL は 18 から使えます。式へのインデックスは CREATE INDEX ON orders ((式)) と書き、こちらは古いバージョンから使えます。
まとめ
WHERE で列に関数をかけているクエリは、その式を生成列にしてインデックスを貼るか、式に関数インデックスを貼れば、クエリを書きかえずに速くできます。生成列は VIRTUAL なら容量を使わず、STORED なら値をディスクに持ちます。JSON の値に関数インデックスを貼るときは、CAST で長さを決めて COLLATE utf8mb4_bin を付けてください。インデックスが使われたかは、最後に EXPLAIN の type と key で確かめます。
もっと学ぶなら【PR】
- MySQL徹底入門 第5版：MySQL の機能を広く解説した本。生成列や JSON 型などの細かい動きを調べたいときに
※Amazonのアソシエイトとして、当サイトは適格販売により収入を得ています。
参考リンク
- MySQL 8.0 リファレンス: CREATE TABLE and Generated Columns
- MySQL 8.0 リファレンス: Secondary Indexes and Generated Columns
- MySQL 8.0 リファレンス: CREATE INDEX Statement（Functional Key Parts）
アイキャッチ画像: Photo by Katie Harp on Unsplash
