Execution plans & indexes

Join conversion safety

A LEFT JOIN is converted to an INNER JOIN only when its WHERE predicate rejects NULL rows from the optional side. Direct strict comparisons, scalar-bound IN/BETWEEN lists and a strict comparison inside NOT can provide that proof. A comparison inside CASE or a scalar subquery does not prove that the outer predicate rejects NULL rows.

Reading the plan visual

The report renders the execution plan for both the original and optimized query as a structured tree: each node shows the operation, the table or index it touches, and row estimates. The original tree is captured before any suggested indexes are applied, so a full scan on the left and an index range on the right is a real before/after, not the same post-index plan drawn twice. Comparing the two trees shows you why the optimized version wins - a full scan replaced by an index range, a filesort disappearing, a join order change.

Stored procedures

MySQL cannot EXPLAIN a procedure as a whole. QueryBolt substitutes the procedure's parameters, then EXPLAINs each standalone statement it can extract — the representative SELECT, nested IF/ELSE SELECTs, and DML such as UPDATE. The Execution plan tab lets you switch among those statements so a join hidden inside an IF branch is visible as its own tree. Parameters change row estimates; they do not invent join nodes that were never in that statement.

Index recommendations

When an index would meaningfully reduce cost, QueryBolt generates the exact DDL and estimates its impact:

ALTER TABLE orders ADD INDEX idx_user_id (user_id);

Compound MySQL anti-probes

For supported NOT EXISTS queries with multiple constant equality filters, QueryBolt explores a key beginning with those filters as an alternative to a correlation-first key. This gives the engine a candidate for materializing the matching child IDs. The original query is preserved, and only plan-used, measured candidates are retained.

Applying recommendations safely

Recommendations are suggestions, not automatic changes - QueryBolt has no access to your database. Review the estimated impact, check for overlap with existing indexes, and consider write-amplification cost before running DDL against production. QueryBolt does not recommend an index that another recommended index already covers (its key a prefix of the other, its INCLUDE columns contained in it); the final benchmark measures the remaining set, so what you see is what was measured. On SQL Server, when your table definitions already have a plain nonclustered index on the same key, or on the leading columns of the recommended key, the DDL rebuilds that index in place under its own name (CREATE INDEX ... WITH (DROP_EXISTING = ON), keeping its current INCLUDE columns) instead of adding a second, overlapping index; every lookup the old index served still works on the wider key, and the rollback rebuilds it as it was. The same applies to indexes SQL Server’s own execution plan requests, and a recommendation that an existing index already covers is not made at all. Unique, clustered, filtered and disabled indexes are never rebuilt this way. MySQL has no INCLUDE columns, so there the same situation - an existing plain secondary index whose columns lead the recommended key - is handled with one atomic statement, ALTER TABLE ... DROP INDEX ix, ADD INDEX ix (...), which keeps the index name and is online on InnoDB; the rollback restores the original columns. PRIMARY, UNIQUE, invisible, FULLTEXT, column-prefix and functional indexes are left alone. A rebuild takes time and locks on a large table, so schedule it like any index change. LEFT JOIN promotion requires proof that the WHERE predicate rejects NULL-extended rows. A comparison merely appearing inside CASE, a scalar subquery, or a text literal is not that proof; complex or unproven cases retain their LEFT JOIN.

Why a rewrite sometimes isn’t shown

On a benchmarked run, QueryBolt only shows a rewrite that ran measurably faster than your query. A rewrite that is correct but was not faster is left out of the result entirely rather than shown as a suggestion, and so is a rewrite QueryBolt considered but did not choose, because applying a change should make the query actually run faster, not just produce a different-looking plan. When nothing measured faster, your query is reported as already in good shape. Preview-only workloads (see Preview-only workloads) show rewrites without a benchmark and say so. Index recommendations follow their own rules (see below).

Why an index sometimes isn’t suggested

Index suggestions are benchmark-qualified: if adding the index didn’t measurably help on your workload shape and declared row counts, it isn’t recommended. An index that sharply reduced rows examined is still recommended when the query did not get faster, as a reads improvement; it is not recommended when it ran slower beyond the tolerance in two separate measurements (see How a win is decided). Larger row counts and selective predicates make real index wins much easier to detect; a data profile (see Schema & row counts) makes the test data as selective as yours.