SQL Optimization Strategies & Reference

What is parsesql.io?

parsesql.io is a free, browser-based tool for formatting, parsing, and analyzing SQL. Paste a query, pick a dialect (or let it auto-detect), and you get back:

Formatted SQL — consistently pretty-printed, dialect-aware.
The parsed AST — a collapsible JSON tree of how your query was parsed.
Optimization opportunities — heuristic checks for common anti-patterns that hurt query performance or readability.

Supported SQL dialects

parsesql.io recognizes and formats SQL from twelve major engines:

BigQuery — Google's analytical warehouse (UNNEST, SAFE_CAST, ARRAY_AGG, STRUCT).
PostgreSQL — open-source RDBMS (::casts, RETURNING, ON CONFLICT, generate_series).
MySQL — popular OLTP database (GROUP_CONCAT, LIMIT offset/count, AUTO_INCREMENT).
Snowflake — cloud data warehouse (IFF, LATERAL FLATTEN, VARIANT, PARSE_JSON).
SQL Server (T-SQL) — Microsoft's SQL dialect (TOP, [bracket] identifiers, GETDATE).
Amazon Redshift — AWS data warehouse (DISTKEY, SORTKEY, ENCODE).
DuckDB — in-process analytical engine (SUMMARIZE, EXCLUDE, COLUMNS(*)).
Oracle — enterprise RDBMS (DUAL, ROWNUM, CONNECT BY, NVL).
SQLite — embedded database (AUTOINCREMENT, PRAGMA, WITHOUT ROWID).
Trino / Presto — distributed SQL query engine.
Apache Spark SQL — Spark's SQL dialect (LATERAL VIEW, EXPLODE, CLUSTER BY).
ClickHouse — columnar OLAP database (FINAL, SAMPLE, MergeTree engines).

Optimization checks

The analyzer walks the parsed AST and flags common anti-patterns:

SELECT * — fetches every column; breaks downstream consumers and prevents index-only scans.
LIKE '%foo' — leading wildcards prevent b-tree index usage.
NOT IN (subquery) — returns no rows if the subquery contains NULL; prefer NOT EXISTS.
DISTINCT combined with GROUP BY — redundant deduplication.
Comma-separated FROM — implicit cross joins, prefer explicit JOIN ... ON.
Function on indexed column in WHERE — prevents index usage.
UNION vs UNION ALL — UNION deduplicates; ALL is faster when disjoint.
col = NULL — always UNKNOWN; use IS NULL.
ORDER BY in a subquery without LIMIT — wasted sort work.
Very large IN lists — better as a join or VALUES clause.
HAVING without an aggregate — push to WHERE.
Scalar subqueries in SELECT — typically run per output row.

Optimization strategies

The same query can be fast on one engine and catastrophic on another. OLTP databases (PostgreSQL, MySQL, SQL Server, Oracle) optimize for small-result, index-driven workloads. Big-data engines (BigQuery, Snowflake, Redshift, Spark) scan billions of rows across a distributed cluster and bill you for the bytes touched. Strategies that win in one world often lose in the other.

Relational (OLTP) databases

Focus on response latency, lock contention, and avoiding the table scan. Indexes are your primary lever.

Use the right indexes — and let them do their job

A correctly-chosen index turns an O(N) table scan into an O(log N) tree walk. Build composite indexes that cover the columns in your WHERE and ORDER BY clauses; consider covering indexes that include the columns you select so the engine never has to read the table at all.

-- index on (status, created_at) makes both predicates indexable
CREATE INDEX ix_orders_active ON orders (status, created_at DESC);

SELECT id, total FROM orders
 WHERE status = 'active' AND created_at > NOW() - INTERVAL '1 day'
 ORDER BY created_at DESC;

Keep predicates SARG-able

A predicate is sargable when the column appears alone on one side of the comparison. Wrapping a column in a function (UPPER, DATE, CAST) hides the value from the index and forces a scan. Apply the transform to the literal side instead, or store a computed column you can index.

-- bad: index on email is useless
WHERE LOWER(email) = 'a@b.com'

-- good: index is used
WHERE email = LOWER('a@b.com')

-- or: index the expression
CREATE INDEX ix_lower_email ON users (LOWER(email));

Pick the join strategy that matches your data sizes

Nested loop wins when one side is small and the other is indexed. Hash join is fastest when both sides are medium-sized and one fits in memory. Merge join wins on huge pre-sorted inputs (common after a GROUP BY). The planner usually picks well — your job is to keep statistics fresh so the cost model is accurate.

-- Force fresh stats so the planner picks the right join
ANALYZE orders;  -- Postgres
ANALYZE TABLE orders;  -- MySQL
UPDATE STATISTICS orders;  -- SQL Server

Paginate with keyset, not `OFFSET`

OFFSET 10000 still reads every row before throwing them away. The cost grows linearly with the page number. Keyset pagination uses the last seen ID as a starting point, which the index can seek to in constant time.

-- bad: cost grows with page number
SELECT * FROM events ORDER BY id LIMIT 20 OFFSET 10000;

-- good: constant cost, regardless of how deep you scroll
SELECT * FROM events WHERE id > :last_seen_id
 ORDER BY id LIMIT 20;

Keep transactions short

Every row a transaction touches is locked until it commits. Long-running transactions block concurrent writers, balloon the undo log, and turn read replicas stale. Open the transaction as late as possible, do the minimum work, and commit. Network calls and user input do not belong inside a database transaction.

Big-data & analytical engines

Now the cost model flips: data is partitioned across nodes, storage is columnar, and you pay per byte scanned (BigQuery) or per slot-second (Snowflake). Indexes don't exist. The lever is reducing how much data the engine has to read and move.

Partition and cluster on the columns you filter

On BigQuery, Snowflake, and Redshift, partitioning lets the engine skip entire chunks of storage when your WHERE clause doesn't need them. Combine with clustering / sort keys on the next most-filtered column. Both are free at write time and cut bytes-scanned by orders of magnitude.

-- BigQuery: partition by day, cluster by tenant
CREATE TABLE events
PARTITION BY DATE(occurred_at)
CLUSTER BY tenant_id
AS SELECT * FROM staging.events;

-- Now this query only reads a few partitions
SELECT COUNT(*) FROM events
 WHERE occurred_at > '2026-04-01'
   AND tenant_id = 42;

Select only the columns you need

Columnar engines store each column separately. SELECT * forces them to read every column file on disk — even the JSON blob nobody asked for. BigQuery and Snowflake bill by bytes scanned, so a tight column list maps directly to a smaller bill. Naming columns also stabilizes downstream consumers when the schema evolves.

-- BigQuery: bytes billed scales with columns AND rows scanned
SELECT id, total, created_at  -- 3 columns scanned
  FROM `proj.ds.orders`
 WHERE DATE(created_at) = CURRENT_DATE();

-- not: SELECT *  -- 40 columns scanned

Aggregate before joining, not after

Big fact tables joined to small dimensions are the classic warehouse pattern — and the classic performance trap when written naively. If the join's only purpose is to add a name or attribute you'll group by anyway, aggregate the fact table first (down to thousands of rows) and join that to the dimension. The shuffle is orders of magnitude cheaper.

-- bad: shuffles 1B rows
SELECT c.country, SUM(o.total)
  FROM orders o JOIN customers c ON c.id = o.customer_id
 GROUP BY c.country;

-- good: shuffles only the aggregated fact
WITH agg AS (
  SELECT customer_id, SUM(total) AS total
    FROM orders GROUP BY customer_id
)
SELECT c.country, SUM(agg.total)
  FROM agg JOIN customers c ON c.id = agg.customer_id
 GROUP BY c.country;

Use approximate functions for counts and quantiles

COUNT(DISTINCT) on a billion-row column has to de-duplicate everything across a cluster shuffle. The approximate variants (APPROX_COUNT_DISTINCT, APPROX_QUANTILES, APPROX_TOP_COUNT) use HyperLogLog / t-digest sketches that fit in tiny state and merge across shards in one pass. Accuracy is typically >99% — fine for dashboards.

-- BigQuery: 100× cheaper at billion-row scale
SELECT
  DATE(occurred_at) AS day,
  APPROX_COUNT_DISTINCT(user_id) AS dau,
  APPROX_QUANTILES(latency_ms, 100)[OFFSET(50)] AS p50,
  APPROX_QUANTILES(latency_ms, 100)[OFFSET(95)] AS p95
FROM events
GROUP BY day;

Watch for skew (one node ends up with everything)

Distributed engines shuffle data by hashing the join / group key. If 90% of your rows share one value (a NULL tenant, a "guest" user, a default category), one node gets 90% of the work and the rest sit idle. Detect skew with the engine's query plan (worker time max vs. avg) and fix it by filtering the hot key out, splitting it into buckets, or rewriting the join.

-- BigQuery: salt the hot key into 32 buckets
WITH salted AS (
  SELECT *,
    IF(user_id IS NULL, MOD(FARM_FINGERPRINT(event_id), 32), 0) AS bucket
  FROM events
)
SELECT user_id, bucket, COUNT(*)
  FROM salted GROUP BY user_id, bucket;