How do you approach SQL Optimization: Covering Index for SELECT-Only Queries?

Walk WHERE, then ORDER BY, then SELECT, adding each column once.

What are common mistakes on SQL Optimization: Covering Index for SELECT-Only Queries?

Filtering on an aggregate in WHERE rather than HAVING. An inner join where a left join is meant, which silently drops the rows the question was about - the customers with zero orders. COUNT(*) versus COUNT(column). The second skips nulls, and that difference is often the entire question.

gitGood.dev

← Back to problems

SQL Optimization: Covering Index for SELECT-Only Queries

HardDatabases

Description

This problem is paper-only - see explanation for the canonical SQL reasoning.

A covering index includes every column the query touches (SELECT, WHERE, ORDER BY, GROUP BY) so the engine can answer from the index alone, skipping the heap / clustered table read. The auto-grader validates which columns must be in the index.

Given a query parsed into { where: string[], orderBy: string[], select: string[] }, return the minimum set of columns needed for a covering index, with this layout:

WHERE columns first (deduped, original order),
ORDER BY columns next (deduped against WHERE, original order),
Remaining SELECT columns last (deduped against the previous two, in input order). These can go in INCLUDE clause for engines that support it (Postgres / SQL Server).

For the auto-grader: implement coveringIndexColumns(parsed) returning the column array.

Example 1

Input: parsed = { where: ["tenant_id"], orderBy: ["created_at"], select: ["id","title","tenant_id"] }

Output: ["tenant_id","created_at","id","title"]

Explanation: tenant_id (where) first, created_at (orderBy) next, then id and title from SELECT (tenant_id deduped).

Constraints

- All arrays have <= 10 strings. - Column names are lowercase strings.

SQL Optimization: Covering Index for SELECT-Only Queries: approach, complexity and pitfalls

A hard sql problem, graded against 8 test cases (6 of them hidden).

The pattern: SQL

Query-writing problems - joins, aggregation, window functions, and query tuning.

Reach for it when you see: A schema and a question about the data rather than a function signature.

If you are stuck

Show the 2-step hint ladder

Walk WHERE, then ORDER BY, then SELECT, adding each column once.
Use a Set to track 'already added'.

The optimal approach

Show the approach: Canonical SQL (Postgres - explicit INCLUDE):

```sql

CREATE INDEX idx_articles_tenant_created

ON articles (tenant_id, created_at) -- key columns: filter + sort

INCLUDE (id, title); -- payload columns to avoid heap fetch

```

SQL Server has the same `INCLUDE` syntax. MySQL doesn't have INCLUDE; you bake all columns into the key, paying for a wider index.

What "covering" buys you: a query like `SELECT id, title FROM articles WHERE tenant_id = ? ORDER BY created_at` runs as an index-only scan. No heap I/O. On a 100M-row table this can be 10-100x faster than a non-covering index that requires a heap fetch per row.

Trade-offs:

- Wider indexes use more disk and RAM (buffer pool pressure).

- Every INSERT / UPDATE / DELETE updates each index, so write amplification grows with index count and width.

- INCLUDE columns don't participate in ordering, so they don't widen the seek key - they only widen the leaf pages.

Postgres-specific: EXPLAIN shows `Index Only Scan` when covering works. If it shows `Index Scan` instead, the visibility map may be stale - run `VACUUM`. INCLUDE-style covering requires Postgres 11+.

The full reference solution in every supported language stays in the editor above - reveal it there once you have had a real attempt.

Common SQL mistakes

These apply to the pattern as a whole, not just this problem.

Filtering on an aggregate in `WHERE` rather than `HAVING`.
An inner join where a left join is meant, which silently drops the rows the question was about - the customers with zero orders.
`COUNT(*)` versus `COUNT(column)`. The second skips nulls, and that difference is often the entire question.