index は、database が table 全体を scan せずに rows を素早く見つけるための data structure です（通常は B-tree）。本の索引のようなものです。indexed columns に対する lookup、filter、join、sort を大幅に高速化しますが、追加 storage と slower writes という cost があります。
users email ;
INDEX idx_users_email users(email);
index がない場合、database は full table scan を行います。index があると、B-tree をたどって matching rows を logarithmic time で探せます。数百万 rows の scan と数回の lookup ほどの差になります。
✓ WHERE clause でよく filter される columns
✓ JOIN に使われる columns（特に foreign keys）
✓ ORDER BY に使われる columns（sort に index を使える）
✓ SELECTIVITY が高い columns（distinct values が多い — email, id）
✓ Primary keys（自動 index）と unique constraints
✗ ほとんど query されない columns — storage と write overhead の無駄
✗ 小さい tables — full scan でも十分速く、index の利点が少ない
✗ low-selectivity columns（例: 2 値しかない boolean）— 効果が小さい
✗ write-heavy columns — EVERY INSERT/UPDATE/DELETE が indexes も更新する
→ indexes が多すぎると writes が大きく遅くなる
index は無料ではありません。storage を消費し、writes を遅くします。すべての modification が indexes を更新する必要があるため、over-indexing は write performance を悪化させます。
CREATE INDEX idx_users_country_name ON users(country, name); -- COMPOSITE (multi-column)
-- WHERE country = ? AND name = ? と WHERE country = ? に効く（leftmost prefix）
-- ただし WHERE name = ? だけには効かない（order matters!）
CREATE UNIQUE INDEX ... -- uniqueness を enforce
-- 他にも partial indexes, covering indexes, Postgres の GIN/GiST など
index は database performance の最重要 topic の 1 つです。query を速くする主要な tool であり、scale する database を扱うなら必須知識です。
WHERE、JOIN、ORDER BY、高 selectivity columns へ適切に index を張ると、seconds/minutes の query が milliseconds になることがあります。一方で、index は storage を使い、INSERT/UPDATE/DELETE のたびに更新されるため writes を遅くします。
つまり重要なのは、query されるものに index を張り、過剰に index を張らない balance です。composite index では column order が効く query を決めることも重要です。