問題点5万行のプロダクトテーブルからユニークなカテゴリIDのリストを取得し、最新の更新順に並べ替えたい場面を想定してください。次のようなクエリを実行するかもしれません。
SELECT DISTINCT category_id
FROM products
ORDER BY updated_at DESC;
MySQLはこのリクエストを即座にブロックします。データの代わりに、以下のエラーメッセージが表示されます。
ERROR 3065 (HY000): Expression #1 of ORDER BY clause is not in SELECT list, references column 'products.updated_at' which is not in SELECT list; this is incompatible with DISTINCT
発生理由MySQLは、DISTINCTを処理する際に厳密な集合論理に従います。このキーワードは、重複する行を1つの代表的な行にマージするようエンジンに指示します。
この衝突は曖昧さが原因で発生します。たとえば、category_id = 5の行が3つあり、それぞれ異なるupdated_atタイムスタンプを持っている場合、データベースは最終的なソートにどのタイムスタンプを使用すべきか判断できません。重複が折りたたまれた後、出力に含まれていない「隠れた値」でソートすることは論理的に不可能です。
解決方法### アプローチ1：SELECTリストにソート用の列を追加するエラーを解消する最も簡単な方法は、ソート対象の列をSELECT文に含めることです。これにより、ソート値がユニークなセットの一部となり、曖昧さが解消されます。
SELECT DISTINCT category_id, updated_at
FROM products
ORDER BY updated_at DESC;
注意点： これにより結果が変わることがよくあります。1つのcategory_idに対して5つの異なるupdated_atの値がある場合、そのカテゴリに対して1行ではなく5行が表示されるようになります。タイムスタンプを実際に表示する必要がある場合にのみ、この方法を使用してください。
アプローチ2：GROUP BYを使用する（推奨される方法）このようなケースの9割で、GROUP BYの使用を推奨します。MAX()のような集計関数を使用して、どの値に基づいてソート順を決定すべきかをMySQLに正確に伝えられるため、より柔軟です。
SELECT category_id
FROM products
GROUP BY category_id
ORDER BY MAX(updated_at) DESC;
このクエリはデータベースに対し、「すべてをカテゴリごとにグループ化し、各グループから最新の更新時間を選択し、それらのタイムスタンプでリストをソートする」よう指示します。これは正確で、カテゴリごとに1行だけを返します。
アプローチ3：サブクエリを使用するメインのSELECT文をシンプルに保つ必要がある複雑なレポートの場合は、まずサブクエリで重い処理を行います。
SELECT category_id FROM (
SELECT category_id, MAX(updated_at) as latest_update
FROM products
GROUP BY category_id
) AS sorted_data
ORDER BY latest_update DESC;
予防策とベストプラクティスこのエラーは、MySQL 5.7がデフォルトでより厳格なsql_mode設定を導入した後に一般的になりました。対策として以下を意識しましょう。
- GROUP BYを優先する： ソートロジックが表示されないデータに依存している場合、ほとんどの場合
GROUP BYが適したツールです。- ONLY_FULL_GROUP_BYを尊重する： このモードを無効にすることを勧めるアドバイスをネットで見かけるかもしれませんが、推奨しません。厳格モードは、レポートの不正確な値につながる可能性のある微妙なデータのバグを防ぎます。- 集合論理：DISTINCTは返される行全体に対して作用することを常に忘れないでください。ユーザーに表示されない列は、ソートエンジンからも見えません。## 検証修正を適用した後、EXPLAINでパフォーマンスを確認してください。大規模なデータセットでの「filesort」は避けるべきです。
EXPLAIN SELECT category_id
FROM products
GROUP BY category_id
ORDER BY MAX(updated_at) DESC;
10万行以上のテーブルで、Extra列にUsing filesortと表示される場合は、(category_id, updated_at)の複合インデックスの追加を検討してください。これにより、MySQLはインデックスからソート済みデータを直接読み取ることができ、クエリ時間を数秒からミリ秒単位に短縮できることがよくあります。