Idle answer: Apart from indices required to fulfil constraints (e.g. UNIQUE and FOREIGN KEY), the utility of an index is exposed by it being used in the queries run against the database. This is by principle available only in retrospect. AFAIK SQLite does consider creating an index to run just one statement fast, but only if the cost of creating such an index is expected to be amortised within that single statement. Even if the application programmer has a defined set of queries, there is no guarantee that SQLite will have seen all of said set; and it may well be that the one index never before used is justified by a need for a blindingly fast response to an "emergency query" required to "save the planet from an asteroid strike" (gross exaggeration), in which case Murphy ensures that SQLite will have deleted exactly this index just 5 minutes before it is needed. And then there is still the issue of how much time one is prepared to sacrifice in each and every query for an as yet unknown benefit that may arise from an automatically added/dropped ondex.