Indexes in SQL

An index trades write speed and disk for fast lookups on a column.

CREATE INDEX IF NOT EXISTS idx_orders_customer
    ON orders (customer_id, placed_at DESC);

CREATE UNIQUE INDEX IF NOT EXISTS idx_products_sku
    ON products (sku);

CREATE INDEX IF NOT EXISTS idx_orders_open
    ON orders (placed_at)
    WHERE status = 'pending';

How it works

1. Column order in a composite index decides which filters it serves.
2. A partial index covers only the rows you actually query.
3. EXPLAIN QUERY PLAN tells you whether it is being used.

Keywords and builtins used here

• CREATE
• DESC
• EXISTS
• IF
• INDEX
• NOT
• ON
• UNIQUE
• WHERE

The run, in numbers

Lines

9

Characters to type

250

Tokens

44

Three-star pace

90 tpm

At the three-star pace of 90 tokens a minute, this run takes about 29 seconds.

Type this snippet

Step 1 of 5 in Indexes, step 3 of 17 in Plans & performance.

← Previous Next →