Benchmarking methodology

Isolated execution

Every benchmark runs in a temporary, isolated database provisioned for that single run - never against your production system, and never sharing state with other runs. The database and its generated data are torn down when the job ends.

What gets measured

Both the original and the optimized query execute against the same generated dataset. QueryBolt captures execution time, rows examined or read, latency percentiles, and engine-reported cost, alongside the execution plan for each version.

Minimum improvement threshold

A run only counts against your plan quota if the optimized query clears the minimum improvement threshold. Below-threshold runs are reported honestly - including “no meaningful improvement found” - and do not consume your quota.

Result set validation

Performance-mode rewrite selection requires a full result comparison, including returned values, duplicate counts, and row order when it matters. Equal row counts alone do not prove equivalence. Large results are hashed incrementally with bounded application memory; if comparison fails or remains unavailable, the original SQL is retained and the candidate remains available as analysis evidence. Preview mode can still show unbenchmarked rewrites for review.

MySQL session settings

Benchmarks preserve the sandbox session SQL mode and optimizer settings. Queries rejected by ONLY_FULL_GROUP_BY are not retried with that restriction disabled. Measurements record the effective SQL mode, optimizer switches, connection collation, character set, time zone and engine version when available; missing configuration evidence is reported explicitly. These describe the sandbox, not a verified copy of your production session. Automatic relaxed GROUP BY compatibility benchmarking is unsupported.

Index benchmark comparisons

Individual index trials use a fresh no-index baseline and the same warmup and sample profile on both sides: one warmup and three measured executions by default. Procedure trials refresh each eligible statement baseline too. Indexes the engine’s plan does not use - by MySQL EXPLAIN or SQL Server’s execution plan - are recorded as unused without being measured or consuming the measured-candidate budget. Some indexes only help together: for WHERE email = ? OR phone = ? with neither column indexed, the optimizer ignores either index on its own. Candidates it ignored individually are therefore tried again together, per table and alongside the indexes already kept; the ones the combined plan reads are measured as a group and kept or rejected together. On SQL Server, the indexes the optimizer itself asks for in the query’s plan (its missing-index requests, including INCLUDE columns) join the candidates and are tested first; the result notes when a recommended index came from that request. When that budget leaves candidates untested, the result reports incomplete exploration and the omitted candidates — not a validation error to resolve. QueryBolt records the timing-sample consistency alongside each retained or rejected candidate. On MySQL, a warmup that reaches the server statement time limit ends that measurement immediately with unavailable evidence and no timing samples. An unmeasured candidate does not qualify from that failure.

When index benchmarking does not run

Index candidates can only be benchmarked when QueryBolt can measure the optimized query in its sandbox against usable data. Benchmarking is skipped when the optimized query cannot be measured, no executable optimized SQL is available, or the generated sandbox data fails quality checks. Any index candidates are then kept as structural suggestions only. The result page, the PDF and Markdown reports and the CSV export all label these Not benchmark-validated. QueryBolt has not measured whether they make your query faster, so test them against representative data before deploying them.

Final SQL and indexes

For single SELECT/WITH jobs, the final SQL and proposed index set are validated together before the sandbox closes. A combined rewrite-and-index result is kept when the rewrite independently qualifies, or when the rewrite is what lets the index work - a date or ISNULL rewrite is rarely faster on its own but lets the database seek. That pair is kept only when the rewrite returns identical results and is not slower on its own, and when, with the same indexes, it beats the original query by the minimum improvement; the result then says to deploy the two together. Indexes cannot conceal a slower rewrite. If the combined change fails, QueryBolt can test the rewrite alone, the original SQL with the tested indexes, or the unchanged original. Every accepted fallback receives a fresh measurement and full result comparison. Headline metrics describe the retained SQL; rejected attempts keep separate evidence. Partial index creation is rolled back, and failed temporary-index cleanup fails the job. If even the original cannot be validated, final metrics are unavailable.

Procedure index measurements

Procedure index candidates are tested across eligible standalone SELECT/WITH statements, including unchanged statements, and across the read side of INSERT ... SELECT, UPDATE and DELETE statements, including those inside IF/ELSE blocks: the SELECT that finds the rows the statement writes. The write itself is never run or rewritten, and no index is suggested on the table an INSERT writes to, since there it would only add write cost. A procedure made only of such statements is benchmarked through their read sides. A statement that fails to execute is left out of that measurement; the other statements are still measured, and the result says how many candidates were actually measured. Qualification uses the same structural signals as single-query index tests: execution time, rows examined, and read cost, with SQL Server preferring logical reads when wall-clock time is flat. The final index set is measured together before results are saved. A fully measured combined set that stays below the improvement threshold is recorded as a completed rejection, not incomplete validation. Headline totals count each measured statement once; they do not predict whole-procedure runtime or account for branches, loops and dependent state. Saved evidence identifies omitted units. Failed measurements or DDL, and failed temporary-index cleanup, remain errors; cleanup failure fails the job. Synthetic fixtures still use a representative predicate context, so production-like parameter scenarios remain important.

Procedure benchmark inputs

Standalone procedure benchmarks use declared input parameters with synthetic typed values. Column names are not treated as parameters merely because they begin with p_ or v_. Local variables and detected modified parameters are withheld when their value at the SELECT is unknown. Those units report a skipped benchmark; independent units can still be measured. Explicit preview guidance remains available. Actual defaults, branch-dependent values and production parameter scenarios are not yet modeled.

Covering SQL Server ranges

For a single-table filter with equality predicates and a range on one column, QueryBolt can propose a composite key with projected columns in INCLUDE. This includes keyset pages and supported clock-relative date bounds. The engine must use the candidate, and measurements must qualify it before it is retained.

Index discovery and manual designs

General predicate and filter-covering index discovery also examines SELECT scopes inside CTEs, derived tables, UNION branches and subqueries. Each scope resolves its own table aliases; specialized index patterns may have narrower coverage. On SQL Server, a subquery that totals a child table once per outer row, such as (SELECT SUM(o.Total) FROM Orders o WHERE o.CustomerId = c.CustomerId), is checked for an index on the join column that INCLUDEs the totalled column, so the total is read from the index alone. Suggestions that need generated or computed columns are manual design guidance until the column expression, type, collation and index can be validated together. QueryBolt does not automatically create those columns or emit indexes on invented column names.

SQL Server measurements

Each benchmark uses one reserved connection in a READ COMMITTED transaction that is rolled back after measurement. Elapsed time measures the query request, including network and result transfer, and excludes counter probes. Logical reads and CPU are session-counter deltas that include probe overhead. Warmup runs execute those probes as well as the query, so the one-time compilation cost of the probe is never counted in a measured sample. Unavailable counters remain unavailable; changed sessions or reset counters invalidate measurement. Results retain the measurement source and context.

How a win is decided

The final available measurements take precedence over earlier individual index or rule wins. MySQL normally uses execution time; below the default 10 ms timing noise floor it prefers rows examined and other plan metrics. SQL Server prefers logical reads. When timing is the deciding metric, QueryBolt compares every original and candidate sample pair and requires at least 80% to favor the candidate; an improved median with heavily overlapping samples is kept as evidence but does not qualify. A deterministic drop in rows examined (logical reads on SQL Server) can still support an index result; when that is what qualified an index, the result headlines that reduction rather than a metric that did not move. MySQL read cost, a weighted count of index and table reads, is not enough on its own: it counts a row read through an index as far cheaper than a scanned row, so an index that reads the same rows as a table scan can look much better than it runs. When read cost is the only evidence for an index, QueryBolt re-measures it on larger data (the tables your query reads, grown to the escalation size within your plan's row limits) and keeps it only if it runs measurably faster there or examines genuinely fewer rows. If you set row counts yourself, that re-measurement does not grow the data past them. A runtime regression can veto a structural win. The tolerance is the larger of 1 ms and the improvement threshold applied to the original runtime — on a 40 ms query, 2 ms — and it scales with the query, so the same rule applies to a 3 ms query and a 3 s one. One measurement beyond that tolerance does not decide it: QueryBolt measures a second round in the opposite order (the index first, then a fresh run of your original query). Slower beyond the tolerance in both rounds is a repeatable regression and the index is not kept, however much logical reads or rows examined improved, on a query of any size. Beyond the tolerance in one round and within it in the other is inconclusive, and the index is kept for its reads. If the second round cannot be measured, that is missing evidence, not a pass: the index is not kept. The second round is used when each index is tested on its own and again when the final set is measured together. Three other checks are stricter and have no second round: indexes that only help as a group, the re-measurement on larger data, and stored-procedure statements. There, an index that reads less but measured slower beyond the tolerance once is simply not kept. An index kept this way that did not also run faster is presented as a reads improvement, not a speed improvement: the result leads with the reduction in logical reads or rows examined and states the execution times as measured, including a small slowdown inside the tolerance. The default improvement threshold is 5%; changes at or below it do not qualify. An index that improves only wall-clock time, with no drop in rows examined, read cost or logical reads, must also save at least 1 ms: on a sub-millisecond query a few hundredths of a millisecond is measurement noise, the same allowance that keeps such a change from counting as a slowdown. When a single index’s verdict rests on timing alone and lands within 5 points of that threshold, QueryBolt measures it a second time in the reverse order (index first, then the original) and decides on both rounds together, so one noisy round does not decide it; if the second round cannot be measured, the index is not kept. Selection and presentation are separate: a regression can be small enough that the result page will not label it slower, and still be large enough against the allowance to stop the candidate being kept. The result page does not present sub-floor wall-clock deltas as faster or slower.

Empty result sets

If both the original and optimized queries return no rows from populated test tables, the run cannot qualify as a billed optimization. Index candidates that still show a large drop in rows examined or read cost are kept for review rather than discarded because a few-millisecond empty run looked slower. The result page does not present an empty-versus-empty digest as a full match, because equivalence was not tested on real rows. Providing schema, representative parameter values, and production-scale row counts makes these benches match real rows.

Preview-only workloads

Some workloads are optimized in preview mode without a full benchmark: CREATE VIEW definitions and function/trigger bodies. A SQL Server temp-table staging script is benchmarked when you supply table definitions, and falls back to preview when there is no schema to execute against or when it stages into a global ##temp table or a @table variable. Read-only query scripts can be benchmarked one statement at a time when execution is available. The report always states which mode a run used and why.