Improve a query with evidence

Improve a query only after you can show what is slow and what the correct answer should be. A faster statement is not an improvement if it drops required records or changes the meaning of an aggregate.

Keep a baseline#

Save the original query in a Scratchpad cell or file. Record the source environment, result count, and observed runtime. Choose a development or test environment suitable for the cost of the experiment.

Identify a few values that must remain correct, such as the count for a known user or the total for a small date range. These become checks for every revision.

Reduce unnecessary work#

Return the columns and records needed by the question. Check the join keys and whether joining to another object creates multiple rows for one business record.

If the required answer is a summary, calculate that summary at the source instead of transferring every input row to a spreadsheet. Keep a separate sample query when you need to inspect individual records behind the total.

Inspect the plan#

Use your database's explain syntax in Scratchpad, or the CLI's explain command where supported. For example, PostgreSQL can show a plan for a read without using its executing ANALYZE option:

EXPLAIN
SELECT id, username
FROM test_schema.users
WHERE id = 1;

Plan syntax and fields vary by engine. Some explain options execute the statement, so check the database reference before applying them to a write or an expensive read. See PostgreSQL's explain guide or MySQL's explain guide.

Change one thing and compare#

Keep the revised query in a separate cell. Run the same correctness checks and compare the result and timing to the baseline. If several things change at once, it is harder to tell which change helped or introduced a difference.

An index or schema change affects more than the current editor. Put it through a reviewed migration and assess the other queries that use the object before treating a single faster result as sufficient evidence.