Schema & row counts

Why row counts matter

Benchmarks run at the scale you declare, not the scale of an empty table. QueryBolt generates synthetic, query-aware test data sized to your row counts, shaped the way production data usually is: each filter matches about the share of rows it typically would (a one-week date window is a small slice of years of history, a status value one of a few, a lookup by email a handful of rows; a window relative to now, such as GETDATE() or NOW() minus 30 days, is measured from the moment your job runs, by the benchmark database server’s own clock; dates that no filter decides are generated the same way every time, so the same query gets the same data apart from the columns such a window places), and joined child rows spread across all their parents rather than only the ones your filter selects. A measured result applies to that generated scenario; production data may have different skew, correlations, NULL rates, duplicates, join fanout, and parameter selectivity.

Uploading table definitions

Paste CREATE TABLE statements or upload a schema file in the wizard’s second step. Unique, fulltext, and spatial indexes, plus ALTER VIEW, are accepted alongside CREATE TABLE, CREATE INDEX, and CREATE VIEW. This is optional - QueryBolt infers tables from your query when definitions are missing - but real definitions improve data generation and index analysis significantly. Inferred schemas for stored procedures exclude procedure parameters and local variables so they are not invented as table columns. A column your query compares to a text literal (priority = 'high') is inferred as a character column, whatever its name suggests. On SQL Server, a column your query compares to an N'...' literal is inferred as NVARCHAR, as such code usually runs against; the result notes that assumption, because if the column is really VARCHAR the N prefix blocks index seeks. When your table definitions say the column is VARCHAR or CHAR, the result advises dropping the prefix (for ASCII literals, where that cannot change which rows match). On MySQL, a character column compared to a number (phone = 5551234567) gets the reverse advice: MySQL compares as numbers and cannot look the value up in an index on the column. Quoting the literal restores the index lookup, but check how values are stored first, because the numeric comparison also matches values with leading zeros or spaces that the quoted one will not. On Linux MySQL, table names are case-sensitive: QueryBolt maps query table names onto the exact spelling from your CREATE TABLE statements when it measures, EXPLAINs, and suggests indexes. SQL Server DATEDIFF filters with year, quarter, month, week, day, hour, minute, and second units shape test-date windows when one argument is a column and the other a clock or date literal. Month and week filters use calendar boundaries, not fixed-day approximations. Generated timestamps have second precision; sub-second differences and clock-relative DATEDIFF equality buckets are not modeled. Sub-day filters on DATE columns and seconds filters on SMALLDATETIME are left unmodeled; SMALLDATETIME hour and minute buckets use whole-minute values.

CREATE TABLE users (
  id INT PRIMARY KEY,
  email VARCHAR(255) NOT NULL,
  created_at DATETIME NOT NULL,
  KEY idx_created_at (created_at)
);

Tables with the same name in different schemas

If your definitions include two tables with the same name, such as dbo.Tickets and archive.Tickets (or shop.orders and archive.orders on MySQL), write the table in your query with its schema or database name. QueryBolt then uses that table’s columns, keys and indexes. When the query uses the bare name, it cannot tell which table is meant, so it treats nothing about that table as known: rewrites that depend on a NOT NULL column, a unique key or a column type are not applied.

Pasting a schema export

Paste your schema exactly as your tools export it. On MySQL that is SHOW CREATE TABLE output, mysqldump --no-data output, or a phpMyAdmin export that adds keys with ALTER TABLE. On SQL Server it is an SSMS or Azure Data Studio script from Tasks > Generate Scripts (turn on Script Indexes under Advanced) or Script Table as > CREATE To. Session settings in those files (USE, SET ANSI_NULLS, SET QUOTED_IDENTIFIER, mysqldump's /*!40101 SET ... */ lines) and GO separators are ignored, and foreign keys, filtered indexes, INCLUDE columns and index options are kept. The wizard's How to get your table definitions panel lists the commands for the tables your query reads.

What a supplied schema unlocks

Some rewrites are only correct when a fact about your data is certain, so QueryBolt applies them only with table definitions you supplied, never with an inferred schema: rewrites that need a column to be NOT NULL (such as NOT IN to NOT EXISTS), rewrites that need rows to be unique (such as UNION to UNION ALL or removing a redundant self-join), and removing LOWER()/UPPER() or a trailing-space trim, which depends on the column's collation. Your existing indexes also let QueryBolt widen an index you already have instead of suggesting an overlapping one.

Setting row counts

The wizard lists every table it detected and lets you set an approximate row count for each. Use production-order-of-magnitude numbers - the difference between 1,000 and 1,000,000 rows routinely changes which optimization wins. If you leave the counts at the default, QueryBolt grows the test data up to ten times, within your plan’s limits: on SQL Server always, because at the default size its optimizer rarely uses an index a production table needs; for stored procedures on either engine always, because their statements are measured one by one with no second round; and for a MySQL query when it finishes too quickly to measure. Larger data takes longer to generate, so a procedure bench left at the defaults runs longer than one at 1,000 rows. When a query reads many tables, the starting value is lowered evenly so the total fits your plan. Datetime columns without a recognized filter are spread over three years of synthetic history. A recognized date window or supplied data profile determines its own values; the fixed or query-clock reference still makes unfiltered draws repeatable for the same input.

Plan limits

Every plan starts each table at 1,000 rows unless you set a count. Because 1,000 rows is usually too few to show an index win, tables you did not size are generated at ten times that, within your plan’s limits, for SQL Server queries and for stored procedures on both engines; a MySQL query that finishes too fast on the default data is measured again at ten times the size. A count you set, or a data profile, is never changed. Free allows up to 1,000 rows per table (10,000 total). Core raises that to 30,000 per table (200,000 total), and Max to 200,000 per table (1,000,000 total). Index and join wins often only show at Core-scale benches; Free is sized to try the product. See Pricing for the full comparison.

How long large benches take

Synthetic data is generated row by row, so wall-clock time follows the row budget. Free-scale benches usually finish in a few minutes. A Core bench near 200,000 rows can take around an hour; a Max bench near 1,000,000 rows can take several hours. Jobs run in a queue — you can close the tab and return from Jobs when it completes, or turn on job-completion email under Account Settings.

Use your execution plan

You can optionally paste SQL Server Showplan XML, MySQL EXPLAIN FORMAT=JSON, or MySQL EXPLAIN ANALYZE text in the wizard. Formats are mssql-showplan, mysql-json, and mysql-analyze; the maximum is 512 KiB. QueryBolt retains a whitelist of operator names, table/column names, cardinalities, costs and warning flags, and discards query text, predicates, parameter values and the raw plan. MySQL plan aliases are bound only when they identify one table in the current query. Production hot operators prioritize experiments; SQL Server missing-index requests become candidates only when their columns belong to a table resolved for this query. Production conversions, spills, memory-grant warnings and residual filters appear as review advice. The supplied plan does not qualify a recommendation: sandbox result validation and performance measurement still decide what ships.

One index set for a query script

For a read-only multi-statement script with a usable schema, QueryBolt consolidates overlapping index candidates across statements, measures each candidate against every statement, then validates the exact combined set. A statement that runs slower beyond max(1 ms, 5% of its own baseline) rejects that index set, even if the total improves. A failed statement leaves the complete workload evidence unavailable; it cannot qualify a partial script. Each statement runs separately in the sandbox and contributes once to the totals by default. SQL Server GO separators preserve batch boundaries; semicolon-separated SELECTs within a batch are separate statements too. Unexpected additional result sets leave comparison unavailable rather than validating only the first set. An optional options.workloadProfile, exported by the wizard from SQL Server Query Store or MySQL performance_schema, weights the aggregate by observed execution counts; that aggregate is a weighted mean per invocation. Only fingerprints and counts are retained. Every statement must match, and ambiguous MySQL digests are rejected. Query Store matching uses exact SQL text with or without its final semicolon, so substituted or parameterized variants may not match. Retention and counter resets affect the observed weights; they are not a forecast of future traffic. Scripts are limited to 2–50 independently executable read-only statements; loops and stateful scripts are not modeled, and the combined script is never executed as a batch.

Numeric comparisons in test data

When your query compares a text column with a numeric literal, synthetic test values stay convertible to numbers, including comparisons through functions such as ISNULL(flag, 0). Quoted text comparisons still receive text values.

Matching your real data (data profile)

Row counts set the scale; a data profile sets the shape. In the row-count step, open Match your real data and click Get profile script. QueryBolt builds a single read-only SELECT for your query that you run on your own database or a replica. It returns one JSON value - table row counts, distinct-value counts, NULL fractions, minimum and maximum for numeric and date columns, and the common values of status and flag columns only (a boolean or enum type, or a name such as status, type or is_active; returned only when the column has at most 50 distinct values that each repeat) - and never rows of data. The script header lists every column whose values it returns. Paste that value back and the generated data follows your real table-size ratios, date ranges and value skew, scaled to your plan, so a filter that matches 1% of your rows matches about 1% of the test rows. That is what lets an index that helps in production show its benefit in the benchmark. Tick Also check which indexes on these tables are actually used before getting the script and it also returns your server's own read and write counts for each index on those tables (names and counts only, from sys.dm_db_index_usage_stats on SQL Server or performance_schema on MySQL). The result then lists indexes that are maintained on every write but were not read in those counters. It reports this as an observation and recommends no action: the counters cover at most the time since the server started (or since the table's indexes were last changed), and they can be cleared without any record (on MySQL by truncating the performance_schema summary tables, on SQL Server when the database goes offline or auto-closes), so they show that an index was not read recently, not that it is unused. Check against a period that includes month-end or other periodic work before changing anything. Primary keys, unique indexes and the clustered index are never listed, and neither is an index your query could itself use. It needs VIEW SERVER PERFORMANCE STATE (or VIEW SERVER STATE) on SQL Server or SELECT on performance_schema on MySQL; without it the rest of the profile is still returned. It is best run on the primary: a replica counts only its own reads.

Data lifecycle

Generated test data lives only inside the temporary benchmark database for your run and is dropped automatically when the job completes.