SQL Indexes Cheat Sheet: Make Slow Queries Fast

If your queries feel slow, there's a good chance indexes are either missing or misused.

The good news: you don't need to memorize exotic index types to look strong in interviews or fix real performance issues.

You just need to understand a few core patterns:

• When to add an index (and when not to)
• How composite (multi‑column) indexes actually work
• How to read simple EXPLAIN plans
• Classic index anti‑patterns that silently ruin performance

This guide focuses on those patterns using examples you'll see in data analyst, data engineer, and backend interviews.

1. What an index really is?

An index is just a sorted lookup structure that lets the database jump directly to the rows it cares about, instead of scanning the whole table.

If a table has 10 million rows and your query touches 0.1%, a good index can turn a full table scan into a fast seek.

Interview framing:
"Indexes trade extra storage + slower writes for faster reads on specific query patterns."

Key trade‑offs:

• Pros: huge speed‑ups for SELECTs that filter or join on indexed columns
• Cons: extra disk / memory, and INSERT/UPDATE/DELETE become a bit slower

2. The minimum indexing you should always check

For most OLTP / app databases, you want:

1. A primary key (often id) – usually becomes a clustered index
2. Indexes on:
   • Foreign keys used in joins (customer_id, order_id, etc.)
   • Columns frequently used in WHERE filters or JOIN conditions

Example:

sql
CREATE TABLE orders (
  id           BIGINT PRIMARY KEY,
  customer_id  BIGINT NOT NULL,
  status       VARCHAR(20) NOT NULL,
  created_at   TIMESTAMP NOT NULL
);

CREATE INDEX idx_orders_customer_id
  ON orders (customer_id);

CREATE INDEX idx_orders_status_created_at
  ON orders (status, created_at);

Why this helps:

• Queries that join orders to customers on customer_id use idx_orders_customer_id
• Queries that filter by status and date range can use the composite index on (status, created_at)

3. How composite indexes actually work

Composite (multi‑column) indexes work left‑to‑right.

Index:

sql
CREATE INDEX idx_orders_status_created_at
  ON orders (status, created_at);

Queries that can use it efficiently:

sql
-- Uses index well (status + created_at)
SELECT *
FROM orders
WHERE status = 'completed'
  AND created_at >= NOW() - INTERVAL '7 days';

-- Also fine – leading column matches
SELECT *
FROM orders
WHERE status = 'completed';

Query that does NOT use the index well:

sql
-- Bad: leading column (status) not in predicate
SELECT *
FROM orders
WHERE created_at >= NOW() - INTERVAL '7 days';

Rule of thumb:
For a composite index (a, b, c), the optimizer can only seek efficiently when your conditions start from the leftmost column(s).

4. Covering indexes: when the index is enough

A covering index is an index that contains all the columns a query needs, so the database never has to touch the base table.

Example:

sql
CREATE INDEX idx_orders_status_created_at_amount
  ON orders (status, created_at)
  INCLUDE (amount);

Now this query can be answered entirely from the index:

sql
SELECT created_at, amount
FROM orders
WHERE status = 'completed'
  AND created_at >= NOW() - INTERVAL '30 days';

Why this is powerful:

• Fewer random I/O operations
• Huge win on large tables that are read frequently

Interview tip:
Mention that covering indexes are great for critical dashboards / reports that run often.

5. Classic index anti‑patterns (and how to fix them)

5.1 LIKE '%something' on the left

sql
-- Index exists on (email)
CREATE INDEX idx_users_email ON users (email);

-- But this query can't use it efficiently:
SELECT *
FROM users
WHERE email LIKE '%gmail.com';

The wildcard at the start prevents efficient use of a B‑tree index.

Better:

• Store domain separately (email_domain) and index it
• Use full‑text / search engine for complex patterns

5.2 Functions on indexed columns

sql
CREATE INDEX idx_orders_created_at ON orders (created_at);

-- Index is much less useful here:
SELECT *
FROM orders
WHERE DATE(created_at) = CURRENT_DATE;

Better:

sql
SELECT *
FROM orders
WHERE created_at >= CURRENT_DATE
  AND created_at < CURRENT_DATE + INTERVAL '1 day';

Rule: try to keep the indexed column naked on the left side of the comparison.

5.3 Too many indexes

Every index adds:

• Extra disk usage
• Extra write overhead on INSERT/UPDATE/DELETE

Signs you have too many:

• Dozens of rarely used indexes on the same table
• Similar indexes that differ only slightly (duplication)

You can use pg_stat_user_indexes (Postgres) or engine‑specific DMVs to find unused indexes and remove them.

6. How to talk about indexes in interviews

When asked about performance, you can structure your answer like this:

1. Check query patterns first – are filters and joins using proper columns?
2. Add or adjust indexes:
   • Add indexes on frequent WHERE / JOIN columns
   • Use composite indexes that match common predicates
   • Consider covering indexes for hot dashboards / APIs
3. Watch trade‑offs – mention write overhead and storage
4. Use EXPLAIN to confirm the plan switched from seq scan to index scan/seek

That shows you understand both read performance and operational costs.

7. Where to practice real index problems

The best way to learn indexing is to work with realistic schemas and slow queries you can fix.

On TheQueryLab, you can:

• Run queries against production‑style tables
• Experiment with different WHERE and JOIN patterns
• See how index choices affect execution behavior

Tuning a few real queries will teach you more about indexes than reading ten more theoretical articles.