The Origin Story of Covering Indexes in SQLite

In today’s competitive IT job market, the interview question “What is a covering index?” is ubiquitous. The article introduces the concept with a brief code example:

SELECT *

Surprisingly, even the creator of SQLite, D. Richard Hipp, was unaware of covering indexes until he learned about them from MySQL co‑founder David Axmark. Inspired by the discussion at a PHP conference in Germany, Hipp implemented covering index support for SQLite during a trans‑Atlantic flight.

The interview transcript (with translation) explains the idea:

Richard: I had never heard of a covering index. At a PHP conference in Germany, I met David Axmark, who explained how MySQL uses covering indexes. A covering index contains multiple columns; if a query uses only the leading columns, the needed data resides in the remaining columns of the index, allowing the engine to answer the query using the index alone, without touching the original table.

Adam: It sounds like a key‑value store built on an index.

Richard: Exactly. On the way home, on a lightly‑occupied Delta flight, I opened my laptop and added covering index support to SQLite.

This anecdote illustrates how a simple optimization can dramatically improve query performance by eliminating the need to read full table rows.