How do you shrink a query's SELECT list to fit an index you already have, and what do you gain?
answer
- ask for less, get a cheaper path
- count every column the statement mentions
- WHERE and ORDER BY columns count too
- one stray column spoils it for all rows
- the star can never qualify
basics
~20 sList only columns the index already stores, counting columns used in WHERE, ORDER BY and GROUP BY too. If nothing outside the index is referenced, the engine can answer from the index instead of fetching each matching table row.
solid answer
~40 sTake the index you have — say `(customer_id, order_date)` — and write down every column the statement mentions anywhere, not just in the select list: filter columns, sort columns, grouping columns and projected columns all count. If that whole set fits inside the index, the engine has the option of producing the answer from index entries alone and never fetching the table row for each match. One extra column outside the index removes the option for the entire query. So the author's move is to prune: drop columns the caller does not use, replace `SELECT *`, and if a rarely-needed wide column is the only offender, fetch it separately. Designing the index is a different job; the projection is the part you control in the query.
go deeper
Know that asking for fewer columns can let the database answer from an index without reading the table rows, and that SELECT * never qualifies.
Be able to list the full set of columns that must fit — select list, filters, joins, grouping and sorting — and explain that a single outside column costs a table fetch per matching row.
Demonstrate the deferred-join rewrite for wide columns, and be honest that some engines still inspect rows for visibility, so verify in the plan rather than assuming.
Own the tradeoff between trimming projections and adding index columns: one is free and local to a query, the other is a standing write cost paid by every insert and update on the table.
## The rule the query author works with An index entry stores the indexed column values plus a reference to the row. So an index physically contains a subset of each row. If a statement never mentions any column outside that subset, everything it needs is already in the index, and the engine can produce the result without going to the table for each matching row. The moment the statement mentions one column the index does not store, the engine must fetch the row itself — once per matching row. That is a property of the *statement*, not just of the select list. The column set that must fit is the union of: - columns in the `SELECT` list; - columns in `WHERE`; - columns in `ORDER BY` and `GROUP BY`; - columns in `HAVING` and in join conditions. A very common mistake is checking only the projection and being surprised that the plan still fetches rows, because the `ORDER BY` names a column the index does not carry. ## Doing the pruning ```sql -- index: orders(customer_id, order_date) -- mentions total, which the index does not store SELECT customer_id, order_date, total FROM orders WHERE customer_id = 42; -- every mentioned column lives in the index SELECT customer_id, order_date FROM orders WHERE customer_id = 42; ``` The second query is not a different question — it is the same question with the columns the screen does not actually render removed. That is the whole technique, and it is why `SELECT *` is fatal here: the star always names columns outside any index, so a star query never gets this treatment. ## What you gain, honestly stated The saving is the per-matching-row trip to the table. For a filter matching a handful of rows it is negligible. For one matching tens or hundreds of thousands of rows it is the difference between reading a compact run of index entries and scattering reads across the table. The wider the row and the more scattered the matching rows are relative to index order, the larger the difference. Be careful not to over-promise. Some engines cannot always skip the row fetch even when every column is present, because row-visibility information may live with the row rather than the index; the plan then still reports table fetches. Treat the pruned projection as *enabling* the cheap path, not as guaranteeing it. ## When a column genuinely will not fit Sometimes the caller really needs a column the index cannot reasonably carry — a long description on a paginated list, say. Two authorial options: **Split the work.** Let an inner query do the filtering and ordering entirely within the index and produce only key values for the page you want; then join back to the table for just those rows. The expensive part touches only the index; the table is visited a page's worth of times rather than once per match. ```sql SELECT o.id, o.order_date, o.notes FROM ( SELECT id FROM orders WHERE customer_id = 42 ORDER BY order_date DESC FETCH FIRST 20 ROWS ONLY ) AS page JOIN orders AS o ON o.id = page.id ORDER BY o.order_date DESC; ``` **Move the column to a second request.** A list endpoint returns identifiers and short fields; the detail request fetches the wide column for the one row the user opened. ## Verifying rather than believing After pruning, look at the plan and check that the table-fetch step disappeared or shrank. If it did not, the usual causes are a column you forgot was referenced (frequently in `ORDER BY`), a filter the index cannot serve so the whole scan shape is different, or an engine-specific reason the row still has to be inspected. The point of checking is that this rewrite is cheap and its effect is binary: either the statement's whole column set fits the index or it does not. ## What this question is not It is not "how do I design the index" — which columns an index should hold, in which order, and whether to attach payload columns is index-design work with its own write-cost tradeoffs. Here the index is a given and the projection is the variable. Interviewers use exactly this framing to separate people who reach for a new index for every slow query from people who first check whether the query is asking for more than it needs.
- Your list screen needs a column the index cannot hold, but only for the twenty rows on the page. What can you do?Split it: an inner query filters and orders using only index columns and returns the twenty key values for that page; the outer query joins back to the table on those keys to fetch the wide column. The expensive filtering and sorting stays inside the index, and the table is visited twenty times instead of once per matching row.
- Does a column used only in ORDER BY count toward what must fit in the index?Yes. Every column the statement references anywhere — select list, WHERE, join conditions, GROUP BY, HAVING, ORDER BY — must be available from the index for the row fetch to be avoidable. Forgetting the sort column is the most common reason a carefully pruned projection still shows table fetches in the plan.
saying these in an interview costs you the question
- Only checks the SELECT list and ignores ORDER BY columns
- Thinks pruning columns reduces the number of rows scanned
- Assumes any index makes any query index-only
- Claims the saving is large regardless of how many rows match
- Reaches straight for a new index without trimming the projection