How do I fix 'index exists but plan still shows Seq Scan'?

Likely cause: the table is tiny or the filter matches most rows, so scanning is genuinely cheaper. Fix: test on realistic data volumes; the planner is usually right.

How do I fix 'function on the column defeats the index'?

Likely cause: WHERE DATE(created) = ... cannot use a plain index on created. Fix: filter on the raw column, or create an expression index matching the function.

How do I fix 'GROUP BY returns wrong counts'?

Likely cause: NULL handling or confusing WHERE with HAVING. Fix: remember: WHERE filters rows before grouping, HAVING filters groups after.

How do I fix 'query fast in the client, slow in the app'?

Likely cause: different parameters, prepared-statement plans, or network overhead. Fix: run EXPLAIN with the app's actual parameter values.

How do I fix 'CREATE INDEX locks the table'?

Likely cause: a plain CREATE INDEX takes a write lock on some engines. Fix: use CREATE INDEX CONCURRENTLY on PostgreSQL for production tables.

SQL Fundamentals: SELECT, WHERE & Aggregates

I worked through core SQL retrieval, SELECT projection, WHERE filtering, GROUP BY aggregation, and ORDER BY, then added indexes to keep queries fast. Indexing the filtered columns turned full-table scans into millisecond index lookups.

Objective & Context

SQL is the lingua franca of data access. This lab builds query fluency and the habit of checking execution plans, the base for the joins, design, and administration labs.

Learning Objectives

Project and filter rows with SELECT and WHERE.
Aggregate with GROUP BY and COUNT.
Sort and limit result sets deterministically.
Create indexes on filtered columns.
Read EXPLAIN output to confirm index usage.

Environment & Prerequisites

Prerequisites

MySQL 8 or PostgreSQL 16 with a sample schema
A table with enough rows to show index impact (thousands or more)
Access to EXPLAIN for plan inspection
Assumed skill: basic command-line or client usage

flowchart LR F[FROM] --> W[WHERE] W --> G[GROUP BY] G --> H[HAVING] H --> S[SELECT] S --> O[ORDER BY]

Step-by-Step Execution

1. Filter and aggregate

SELECT status, COUNT(*) FROM orders WHERE created > '2026-01-01' GROUP BY status;

Expected output:

(rows of status/count pairs)

What this teaches: WHERE filters before GROUP BY aggregates: the database reduces the working set first, which is why selective filters pair so well with indexes.

2. Add a supporting index

CREATE INDEX idx_orders_created ON orders(created);

Expected output:

CREATE INDEX

What this teaches: The index is a sorted copy of the column with row pointers; the planner can now seek to matching rows instead of scanning the table. Client feedback differs by engine: PostgreSQL prints CREATE INDEX, while MySQL prints Query OK, 0 rows affected; both mean the index was built.

3. Confirm the plan uses the index

EXPLAIN SELECT * FROM orders WHERE created > '2026-01-01';

Index Scan using idx_orders_created  rows=812  (was Seq Scan rows=2.1M)

Expected output:

Index Scan using idx_orders_created  rows=812  (was Seq Scan rows=2.1M)

What this teaches: EXPLAIN shows the plan changed from scanning 2.1M rows to seeking 812; this before/after comparison is the proof the index worked.

4. Sort and limit deterministically

SELECT status, COUNT(*) AS n FROM orders WHERE created > '2026-01-01' GROUP BY status HAVING COUNT(*) > 10 ORDER BY n DESC, status LIMIT 5;

Expected output:

  status   |   n
-----------+-------
 shipped   | 40211
 pending   | 18903
 cancelled |   921
(3 rows)

What this teaches: ORDER BY runs after grouping, so you can sort by the aggregate itself. Adding the unique tiebreaker (status) makes LIMIT deterministic: without it, tied counts can return different rows on each run.

How It Works

A B-tree index stores column values in sorted order with pointers to table rows. Without one, the database must read every row to find matches (a sequential scan); with one, it seeks directly to the matching range, which is why the row count drops from millions to hundreds. In PostgreSQL the leaf entries point to heap tuples; in InnoDB a secondary index entry carries the primary key value instead, so the engine may perform a second lookup to fetch the row. Either way the full scan is avoided.

The query planner chooses the cheapest plan using table statistics, not hints. EXPLAIN reveals that choice: Seq Scan means no usable index, Index Scan means the planner found and used one. Reading plans beats guessing about performance.

Indexes are a trade-off: they speed reads but slow writes, since every INSERT and UPDATE must maintain the index too. The discipline is to index the columns your queries actually filter and join on, then verify with EXPLAIN.

flowchart LR Q["SELECT ... WHERE"] --> P1["EXPLAIN: Seq Scan
2.1M rows"] P1 --> IX["CREATE INDEX
on filter column"] IX --> P2["EXPLAIN: Index Scan
812 rows"]

Validation & Testing

Run the query before and after indexing and compare the execution plan and latency. Pass criteria: correct aggregate results and a plan that switches from a sequential scan to an index range scan.

Advanced: Troubleshooting

Index unused: a function on the column (WHERE DATE(created)) defeats it; filter on the raw column.
Wrong counts: mind NULL handling in aggregates and HAVING vs WHERE.
Slow GROUP BY: index the grouping columns or pre-aggregate.

Troubleshooting

Symptom	Likely cause	Fix
index exists but plan still shows Seq Scan	the table is tiny or the filter matches most rows, so scanning is genuinely cheaper	test on realistic data volumes; the planner is usually right
function on the column defeats the index	WHERE DATE(created) = ... cannot use a plain index on created	filter on the raw column, or create an expression index matching the function
GROUP BY returns wrong counts	NULL handling or confusing WHERE with HAVING	remember: WHERE filters rows before grouping, HAVING filters groups after
query fast in the client, slow in the app	different parameters, prepared-statement plans, or network overhead	run EXPLAIN with the app's actual parameter values
CREATE INDEX locks the table	a plain CREATE INDEX takes a write lock on some engines	use CREATE INDEX CONCURRENTLY on PostgreSQL for production tables

Key Results

Cut a filtered query from a multi-million-row scan to an index range scan.
Returned aggregate reports in milliseconds after indexing.
Verified plan changes with EXPLAIN rather than guessing.
Established index-aware querying as a default habit.

Key Takeaways

SELECT/WHERE/GROUP BY cover most reporting queries.
Indexes turn full scans into seeks on filtered columns.
EXPLAIN is the source of truth for what the planner chose.
Index what you filter and join on; every index taxes writes.
Compare plans before and after to prove an optimization worked.

Practice Challenges

Challenge 1: Slow query hunt

Goal: Find the slowest query in a sample workload with EXPLAIN ANALYZE and fix it with one index.

Hint: Look for Seq Scan on large tables in the plan; index the filter column and re-run EXPLAIN.

Challenge 2: HAVING vs WHERE

Goal: Write a query that finds statuses with more than 100 orders since 2026-01-01, using both clauses correctly.

Hint: WHERE filters rows first, HAVING filters the grouped results after.

SQL Fundamentals: SELECT, Filtering, Grouping, and Aggregates

Objective & Context

Learning Objectives

Environment & Prerequisites

Prerequisites

Step-by-Step Execution

1. Filter and aggregate

2. Add a supporting index

3. Confirm the plan uses the index

4. Sort and limit deterministically

How It Works

How It Works

Validation & Testing

Troubleshooting

Key Results

Key Takeaways

Practice Challenges

Challenge 1: Slow query hunt

Challenge 2: HAVING vs WHERE

Related Labs