Database Query Profiler

A query gets slow for a short list of reasons, and almost all of them come down to how many rows the engine has to touch before the first row reaches your application. Ten milliseconds against 5,000 rows in staging turns into eleven seconds against 40 million rows in production, on the same query text, because the row count changed and nothing else did.

This page reads the statement as text. No credentials, no connection, no round trip. What comes back are the patterns a reviewer looks for first: predicates wrapped in functions, wildcards on the wrong side of a string, a sort with no index behind it, an OFFSET deep enough to make the engine count past a million rows it will throw away.

What the two row counts mean

The two numbers in the report are one model shown twice, under different assumptions.

• Rows read as written assumes no index serves any predicate, so every table in the statement gets scanned end to end. This is the pessimistic case, and on a fresh table with only a primary key, the accurate one.
• Rows read with the index plan applies rough selectivity to each predicate an index could serve: heavy for equality, light for a range, nothing at all for a comparison the engine cannot narrow. It assumes the indexes on the Index plan tab exist.

The gap between them is the argument for building the index. When the two numbers are equal, no index helps at all, and the statement itself needs changing first.

These are order-of-magnitude figures from a fixed selectivity model, not measurements. A column holding two distinct values across ten million rows makes the equality estimate wrong by four orders of magnitude. Read them as a shape, then confirm against EXPLAIN on real data.

Sargable is the word behind most slow queries

Search ARGument ABLE. A predicate is sargable when the engine compares the raw indexed column against a constant, so a B-tree walk lands on the matching range directly. Break that shape and the index is dead weight, even though it exists and looks correct in the schema.

WHERE YEAR(created_at) = 2024
WHERE LOWER(email) = 'sam@northwind.co.uk'

WHERE company_name LIKE '%acme%'

WHERE user_id = '1042'

Column order in a composite index is the whole decision

A composite index on (status, created_at) is not interchangeable with one on (created_at, status). The engine reads left to right and stops at the first column it cannot narrow. That is the leftmost prefix rule, and the ordering it implies has a name: equality, sort, range.

1. Equality columns first. Every column matched with = or IN. Their order among themselves does not matter to the planner.
2. Sort columns next. Columns from ORDER BY, in the order written, with matching direction. Placed here, the index returns rows already sorted and the separate sort step disappears.
3. Range columns last. Anything with >, <, BETWEEN, or a prefix LIKE. A range column consumes the rest of the index, so nothing after it narrows anything.

Put the range column second and the sort still runs in memory. A query filtering status = 'active', ranging on created_at, and sorting by id reads best from (status, id, created_at) even though that feels backwards.

One caution the Index plan tab cannot apply for you. Every index is paid for on write, and duplicates are common: an index on (status) is already contained in (status, created_at) and should be dropped rather than added alongside. Check the existing index list on the table before running anything the tab produces.

Reading a real execution plan

Once you have server access, the plan beats any static guess. Two things to read first.

The number to watch in either engine is rows examined against rows returned. Reading 400,000 rows to return 20 is the definition of a missing index, whatever the total runtime says on a warm cache.

Rewrites worth trying, roughly in this order

• Name the columns.SELECT * blocks covering indexes and drags TEXT and BLOB columns across the wire for no reason. Naming four columns often lets the whole query run from the index without touching the table.
• Replace deep OFFSET with keyset pagination.LIMIT 20 OFFSET 100000 reads 100,020 rows and discards 100,000. WHERE id < :last_seen_id ORDER BY id DESC LIMIT 20 reads 20. The tradeoff is real: you lose direct jumps to page 5,000.
• Split an OR across columns into a UNION ALL. One index cannot serve email = ? OR phone = ?. Two branches, each with its own index, each doing a seek, usually beats the scan. Keep ALL unless duplicates are actually possible, since plain UNION adds a dedup sort.
• Check whether DISTINCT is hiding a join fan-out.SELECT DISTINCT on a join often means the join multiplies rows and the sort is cleaning up afterwards. An EXISTS subquery gets the same result without producing the duplicates first.
• Move non-aggregate filters out of HAVING.HAVING runs after grouping, WHERE runs before. Filtering on a plain column in HAVING groups rows you were going to discard anyway.
• Batch instead of looping. The slowest query in most applications is a fast query executed 5,000 times inside a loop. No index fixes that shape. It gets fixed in the application code with a join or an eager load.

MongoDB follows the same rule with different syntax

Equality, sort, range applies to compound indexes in MongoDB too, and the reordering that matters most is inside the pipeline. A $match placed after $lookup filters documents the server already joined. Move it to the first stage and the join runs against the surviving set instead.

$regex follows the LIKE rule: anchored with ^ it reads a range from the index, unanchored it scans the collection. $ne and $nin match nearly everything, so the planner reads the collection rather than the index. Confirm any change with .explain("executionStats") and read totalDocsExamined against nReturned.

Where this profiler stops

Static analysis has a hard ceiling, and knowing where it sits keeps you from trusting an A grade too far.

• No connection, so no table statistics, no cardinality, no existing index list, and no idea whether the column you filter on holds two distinct values or two million. Selectivity here is a fixed guess.
• Parsing is pattern based, not a full grammar. Deeply nested subqueries, recursive CTEs, window functions, and vendor extensions are read shallowly, and a clause boundary inside a subquery can be misread. Findings on those statements are worth a second look.
• Nothing about the server is visible: buffer pool size, work memory, connection pool limits, lock waits, replication lag, or a cold cache. A query that profiles well here still stalls behind a long running transaction holding a lock.
• The read path is a model of a nested loop join in written order. Real planners reorder joins, choose hash or merge strategies, and sometimes pick a sequential scan on purpose when a large fraction of the table qualifies.
• The rewrite tab makes only mechanical edits it can prove safe on the text. It never guesses column names for a SELECT *, and it will not restructure a query for you. Test any output against real data before it goes near production.
• Write path costs are out of scope. This page has no view of index maintenance on INSERT, fragmentation, or the write amplification of the indexes it suggests.

Next steps in the same workflow

Once the statement is readable, the SQL Formatter makes the clause structure obvious before you start moving predicates around. To try variants against sample data, the SQL Query Tester runs them in the browser.

When the slowness sits in application code rather than the statement, the Code Complexity Analyzer and the Performance Profiler cover that side.

Your query never leaves the browser. Parsing, scoring, and index generation all run in local JavaScript, so no schema names or literals travel to Toolexe or anywhere else.