Which indexes can a city search use?

Apply the leftmost prefix rule to real indexes, and ignore the ones that only look useful.

What to build

A query that lists the indexes on people whose first column is city.

Requirements

• Look at every index on people.
• Keep an index only if its first column, position 0, is city.
• Return the index names in alphabetical order.

Tables

Each test fills these tables with its own rows, then runs your SQL:

CREATE TABLE people (
  id INTEGER PRIMARY KEY,
  name TEXT NOT NULL,
  city TEXT,
  email TEXT
);

Output

One column, index_name, in alphabetical order. Your result is compared with the expected one, column names included, so name each column exactly as asked (use AS).

Constraints

Each test creates its own indexes on people, and some include city in a later position.

Example

When a test starts with:

CREATE INDEX idx_city ON people(city);
CREATE INDEX idx_name_city ON people(name, city);
CREATE INDEX idx_city_name ON people(city, name);

your SQL should print:

index_name
-------------
idx_city
idx_city_name
(2 rows)

Why it works

A composite index is sorted by its first column. If that is not city, rows for one city are scattered through the index, so it gives no starting point for the search.

Examples

CREATE TABLE people (
  id INTEGER PRIMARY KEY,
  name TEXT NOT NULL,
  city TEXT,
  email TEXT
);
CREATE INDEX idx_city ON people(city);
CREATE INDEX idx_name_city ON people(name, city);
CREATE INDEX idx_city_name ON people(city, name);

index_name
-------------
idx_city
idx_city_name
(2 rows)

CREATE TABLE people (
  id INTEGER PRIMARY KEY,
  name TEXT NOT NULL,
  city TEXT,
  email TEXT
);
CREATE INDEX idx_name ON people(name);

index_name
----------
(0 rows)

Each test makes its own tables, runs your SQL on them, and compares what your queries return. Changes never carry over from one test to the next.

Hints

Solution

SELECT m.name AS index_name
FROM sqlite_master m
WHERE m.type = 'index' AND m.tbl_name = 'people'
  AND (SELECT i.name FROM pragma_index_info(m.name) i WHERE i.seqno = 0) = 'city'
ORDER BY m.name;

Lesson: Indexes and how queries find rows →