Finding missing indexes with the Index Advisor¶

Explaining a query plan tells you how SQLite will run a query. The Index Advisor answers the next question: which index would make it faster? It asks SQLite itself — the same advisor the sqlite3 command-line shell runs as .expert — so the suggestions are the indexes SQLite's own query planner would actually use.

To get advice for a query:

Go to the SQL tab and put the cursor in the statement you want advice on, or select several statements.
Click Index Advisor beside Explain Query Plan, or choose SQL → Index Advisor, or right-click in the editor and pick it there.

The Indexes tab opens below the editor, next to Results and Plan.

What you see¶

For each statement:

Suggested indexes — the CREATE INDEX statements that would help, or No new index would help this statement when the existing indexes already serve it.
Plan with the suggested indexes — the query plan SQLite would use once those indexes exist, so you can see what changes before creating anything.

The suggested names follow the pattern idx_<table>_<columns>, the same one Create Index... on the Plan tab uses, and are always unique in the database.

Create Indexes... opens the suggested statements in a new SQL tab. Nothing is created until you run them, so you can rename an index, or leave one out, first. With a single index selected in the list, the button opens just that one; double-clicking an index does the same.

Copy puts the advice on the clipboard as indented text.

How it works¶

The advisor copies the schema of the database into a scratch database of its own, prepares each query there to see which columns the WHERE, ORDER BY and join clauses would want indexed, and builds candidate indexes for them. It then gathers statistics for the candidates from the real data and prepares the queries a second time. An index SQLite's planner then chooses to use is a recommendation; one it ignores is not.

Nothing in your database is changed. The statistics pass does read your tables — all of each table a candidate index is on — so on a large database the analysis can take a while. The progress strip beside the buttons shows it is running, and Stop ends it.

Things worth knowing¶

Only the main database is analysed. A statement that names a table in an attached database comes back as a "no such table" message.
The advice is only as good as the statements you give it. An index that speeds up one query costs a little on every insert, update and delete of the table, so give the advisor the queries that matter together — select them all and ask once — rather than adding every index each one asks for.
The advisor is built into the SQLite library that ships with SQLite Expert. If you have chosen a different SQLite library that was not built with it, the command is greyed out.