Performance
Slow queries: measure them before you add an index
A slow query is measured with EXPLAIN QUERY PLAN and five runs. On 200,000 SQLite rows a full scan costs 13.2 ms, an index 0.29 ms.
Key points
- Before you create an index, read EXPLAIN QUERY PLAN: SCAN is a full read, SEARCH is an index.
- The useful number is the median of five runs, not the first one you saw.
- On 200,000 SQLite rows an index on one column takes the query from 13.2 ms to 0.29 ms.
- A LIKE with a leading wildcard ignores the index: 52.49 ms even when the index exists.
A slow query is measured before you touch it: EXPLAIN QUERY PLAN tells you whether the database reads the whole table, and five repeated runs give a median you can argue about. On 200,000 SQLite rows the same condition goes from 13.2 ms to 0.29 ms once the right index exists.
Three numbers before an index
| What you measure | Where you read it | What it tells you |
|---|---|---|
| Execution plan | EXPLAIN QUERY PLAN | SCAN is a full read, SEARCH is an index |
| Median time | five runs, median | the number the user feels |
| Rows read | EXPLAIN QUERY PLAN, detail field | whether the filter is really selective |
The command
sqlite3 catalogue.db <<'SQL'
.timer on
EXPLAIN QUERY PLAN SELECT id FROM components WHERE library = 'lib-1000';
SELECT count(*) FROM components WHERE library = 'lib-1000';
CREATE INDEX idx_library ON components (library);
SELECT count(*) FROM components WHERE library = 'lib-1000';
SQLThe command needs the sqlite3 client and a test file holding the components table. .timer on prints the duration of every statement: the first SELECT reads the whole table, the second uses the index created in between. Our measurement used the same schema with node and sqlite3, because we needed the median of several runs, not a single one.
When an index does not help
An index is not a magic wand: it answers equality and ranges on one column, not a LIKE '%chart%'. The leading wildcard forces the engine to read every row, and that explains the measured 52.49 ms. It does not help on a column with two distinct values either: half a table is still half a table. There you change the model, add a lookup table, or accept the cost.
The limitation to name
The table lived in memory, with no disk I/O and no concurrency: the absolute milliseconds are lower than on a real server. The ratio between full read and index is what you carry home, not the absolute value. A production database adds cache, locks and concurrent queries, and one more index slows every write.
Where to continue
The EneUI catalogue indexes 111,985 components, 1,556 blocks and the record spotlight card, a card that costs one query per request. To see how much server response time weighs, read TTFB: what it measures; for the cache that skips the query, Cache-Control explained; and for the comparison between architectures, frontend frameworks compared.
Frequently asked questions
How do I know whether a query uses an index?
Read EXPLAIN QUERY PLAN: SEARCH with USING INDEX means the index is used, SCAN means a full table read. The plan shows the route, not the timings.
How many runs give a reliable time?
Five, with the median as the result. The first run pays for the cold cache and engine startup, so it is always the slowest and the most misleading.
Why does LIKE '%chart%' stay slow with an index?
Because the leading wildcard blocks the index order: the engine reads every row and compares the text. An index needs a known prefix.
Does an index slow writes down?
Yes. Every INSERT, UPDATE and DELETE also updates the index, so the cost moves to writes. It pays when reads far outnumber writes and the filter is selective.
Does a local measurement hold in production?
No. Cache, disk, concurrency and real data size change the number. Read the plan locally and time the query on the production database with the same parameters.