IAE Academy

ISRO CS 2025 Q.45: solution and answer

ISRO 2025 · CSQ.45 · 1 mark · MCQ

Consider a table users(userid, country, city, street) with 50 million users. DBA creates the following index for the table

CREATE INDEX myindex ON users(country, city, street)

For which of the following queries will this index be least useful?

  1. (A)

    SELECT userid FROM users WHERE country='IN'

  2. (B)

    SELECT userid FROM users WHERE street='MG Road'

  3. (C)

    SELECT userid FROM users WHERE country='IN' AND city='Pune' AND street='MG Road'

  4. (D)

    SELECT userid FROM users WHERE country='IN' AND city='Pune'

See the question in the paper
ISRO CS 2025 question 45Tap to open full size

Answer and solution

ANSWER

The index is least useful for option (b), the query that filters only on street.

Official answer: BSource: ISRO's final answer key of 15 May 2026

  1. A composite index on stores its entries sorted by country first, then by city within each country, then by street within each city.

  2. Because of this order, the index can be searched directly only when the query fixes a leftmost prefix of the columns: country, or country and city, or all three (the leftmost-prefix rule).

  3. Option (a) filters on country, the first column. This is a one-column prefix, so the index jumps straight to the block of entries for IN.

  4. Option (d) filters on country and city. This is a two-column prefix, so the index narrows the search further than in (a).

  5. Option (c) filters on all three columns. This is the full key, so the index gives a direct point lookup. The index is most useful here.

  6. Option (b) filters only on street, the last column, with no condition on country or city. Entries with street='MG Road' are scattered through the index, one group under every country and city, so the sort order cannot be used to locate them.

  7. To answer (b) the DBMS must scan the whole index or the whole table of 50 million rows. The index helps least for query (b).

KEY IDEA

A multi-column index is ordered by its first column, then the second, then the third, so it helps only when the query's conditions cover a leftmost prefix of the index columns.

WATCH OUT FOR

Thinking that any column named in the index can be used on its own. A condition on the last column alone, without the columns before it, cannot use the index order, so it falls back to a scan.

From the ISRO CS 2025 paper. Every question of this paper · The full paper (PDF, ISRO) · All 1 ISRO Indexing question, with notes