Advanced SQL Tutorial

Advanced SQL Tutorial

MemCP accepts MySQL-dialect SQL over the MySQL protocol and /sql/<database>; PostgreSQL syntax is available through /psql/<database>. The examples below assume a database selected by the connection or URL.

This tutorial combines SQL techniques with the corresponding MemCP planner behavior. A described optimization is a possible physical choice, not a promise for every data distribution. Statistics, selectivity, indexes, ordering, row counts, and LIMIT can change the selected plan.

Atomic counters through unique-key checks

Consider a counter table whose key identifies the event or object being counted:

<syntaxhighlight lang="sql">CREATE TABLE counters (

   id INT PRIMARY KEY,
   cnt INT NOT NULL

);

INSERT INTO counters VALUES (1, 2), (2, 4);</syntaxhighlight>

The MySQL form can create a missing counter or increment an existing counter in one atomic statement:

<syntaxhighlight lang="sql">INSERT INTO counters VALUES (2, 1) ON DUPLICATE KEY UPDATE cnt = cnt + VALUES(cnt);

SELECT id, cnt FROM counters ORDER BY id;</syntaxhighlight>

id	cnt
1	2
2	5

The primary key already supplies the required uniqueness check. The statement avoids a client-side read-modify-write sequence such as SELECT followed by UPDATE, where another transaction could change the counter between both statements.

The PostgreSQL-syntax endpoint provides the corresponding ON CONFLICT form:

<syntaxhighlight lang="sql">INSERT INTO counters VALUES (2, 1) ON CONFLICT (id) DO UPDATE SET cnt = cnt + excluded.cnt;</syntaxhighlight>

Use an explicit transaction when several statements must succeed or fail as one unit:

<syntaxhighlight lang="sql">BEGIN; UPDATE accounts SET balance = balance - 100 WHERE id = 1; UPDATE accounts SET balance = balance + 100 WHERE id = 2; COMMIT;</syntaxhighlight>

An atomic statement and a multi-statement transaction solve different problems: the upsert protects one logical write, while the transaction groups several dependent writes. See Transactions and Isolation.

Joins, grouping, and windows

The following query joins customers with their recent orders, aggregates revenue, and ranks the resulting groups:

<syntaxhighlight lang="sql">SELECT c.id,

      c.name,
      SUM(o.amount) AS total,
      RANK() OVER (ORDER BY SUM(o.amount) DESC) AS position

FROM customers AS c JOIN orders AS o ON o.customer_id = c.id WHERE o.created_at >= '2026-01-01' GROUP BY c.id, c.name HAVING SUM(o.amount) > 100 ORDER BY total DESC LIMIT 20;</syntaxhighlight>

This shape gives the planner several opportunities: it can derive scan boundaries from the date predicate, reorder inner joins, fuse filtering and aggregation into a scan pipeline, and use ordering information to avoid unnecessary work for the final LIMIT. Window expressions whose order-dependent result is reusable can be backed by an ordered-reduce computed column (ORC).

Subqueries and decorrelation

MemCP supports IN, EXISTS, NOT EXISTS, scalar subqueries, correlated subqueries, and derived tables. Supported correlated shapes are decorrelated into logical stages before physical planning; MemCP does not use a hidden per-outer-row scalar fallback.

IN and EXISTS

<syntaxhighlight lang="sql">SELECT o.id, o.amount FROM orders AS o WHERE o.customer_id IN (

   SELECT c.id
   FROM customers AS c
   WHERE c.active = 1

);

SELECT p.id, p.name FROM products AS p WHERE EXISTS (

   SELECT 1
   FROM inventory AS i
   WHERE i.product_id = p.id
     AND i.quantity > 0

);</syntaxhighlight>

After logical decorrelation, physical lowering can implement membership through a direct indexed probe, a prepared key table, or a query-local RecSet. The cost model compares preparation cost with the expected number of probes and downstream rows. SQL NULL and three-valued-logic rules remain decisive, especially for NOT IN; a truth-only RecSet must not erase UNKNOWN results.

Correlated scalar aggregate

<syntaxhighlight lang="sql">SELECT c.id,

      c.name,
      (
          SELECT SUM(o.amount)
          FROM orders AS o
          WHERE o.customer_id = c.id
      ) AS total

FROM customers AS c ORDER BY total DESC LIMIT 20;</syntaxhighlight>

The correlated aggregate is represented as a decorrelated group stage. Depending on cardinality and reuse, physical lowering can choose direct probes, a prepared group cache keyed by customer_id, or a RecSet-based carrier. Identical nested work can be shared instead of being planned independently for every occurrence.

A scalar subquery returns NULL when no row matches and must reject a result with more than one row unless its shape, such as an aggregate, guarantees scalar cardinality.

Derived tables and UNION

<syntaxhighlight lang="sql">SELECT totals.customer_id, totals.total FROM (

   SELECT customer_id, SUM(amount) AS total
   FROM orders
   GROUP BY customer_id

) AS totals WHERE totals.total > 1000 ORDER BY totals.total DESC LIMIT 10;

SELECT customer_id, amount FROM current_orders UNION ALL SELECT customer_id, amount FROM archived_orders;</syntaxhighlight>

Derived tables and set-operation branches remain logical operators through decorrelation and join optimization. Projections that are not consumed by the outer query can be removed, while required ordering, grouping, distinctness, and limits remain explicit semantic boundaries.

Optimization forms used by MemCP

MemCP separates logical optimization from physical lowering. Logical planning describes relational meaning with query blocks, group stages, and union blocks. Only after decorrelation and join reordering does physical lowering choose scans, indexes, RecSets, caches, computed columns, and executable pipelines.

Optimization	What MemCP can do	Useful diagnostic
Subquery decorrelation	Convert supported correlated scalar, IN, and EXISTS forms into joined logical stages; reuse equivalent stages and remove unused payloads.	EXPLAIN IR
Join reordering	Use selectivity and join connectivity to choose a driver and join order. Small connected join graphs can use exact DPHyp search; larger graphs use a bounded search strategy.	EXPLAIN REORDER
Predicate and projection pushdown	Apply filters near their source and delay or remove columns that are not yet required, reducing rows and materialized width.	EXPLAIN IR, EXPLAIN PHYSICAL
Adaptive access paths	Choose direct scans or compatible equality, range, pattern, and ordering indexes. Index-building evidence is accumulated so tiny or rarely useful indexes are not built immediately.	EXPLAIN PHYSICAL
RecSet algebra	Represent qualifying record IDs compactly and combine candidates using union, intersection, difference, and complement when SQL semantics permit it. The adaptive, shard-parallel implementation is described in RecSets.	EXPLAIN PHYSICAL
Group caches	Prepare keyed aggregate results once when repeated probes amortize the build cost; reuse maintained results when their dependencies remain valid.	EXPLAIN PHYSICAL
ORC columns	Materialize reusable order-dependent reductions for supported window and ordered-computation shapes, with dependency-aware invalidation.	EXPLAIN PHYSICAL
Pipeline fusion and late materialization	Keep filter, projection, and aggregate work close to batched scans and assemble wide rows only when a downstream consumer needs them.	EXPLAIN, EXPLAIN COMPILE
Range-based braking	For compatible ORDER BY/LIMIT plans, stop ordered work when remaining keys cannot improve the requested top-k result. Defer expensive scalar projections until after a bounding limit when semantics allow it.	EXPLAIN PHYSICAL
Parallel shard execution	Execute sufficiently large independent shard batches in parallel while avoiding goroutine overhead for small work units.	EXPLAIN

These optimizations preserve SQL semantics. Physical choices can change after data growth, auto-index creation, cache invalidation, or updated selectivity evidence.

Reading MemCP plans

Both SQL syntax modes expose the planner phases:

<syntaxhighlight lang="sql">EXPLAIN SELECT ...; EXPLAIN IR SELECT ...; EXPLAIN REORDER SELECT ...; EXPLAIN PHYSICAL SELECT ...; EXPLAIN COMPILE SELECT ...;</syntaxhighlight>

• EXPLAIN shows the optimized executable Scheme plan.
• EXPLAIN IR shows the decorrelated logical representation before physical artifacts are introduced.
• EXPLAIN REORDER shows the optimized logical order and its planning facts.
• EXPLAIN PHYSICAL shows physical decisions and the selected executable plan, including alternatives such as direct probes, RecSets, and prepared carriers.
• EXPLAIN COMPILE reports phase timings and plan-shape metrics for parsing, decorrelation, reordering, physical preparation, emission, and final optimization.

For example, compare the phases of a selective ordered membership query:

<syntaxhighlight lang="sql">EXPLAIN IR SELECT o.id FROM orders AS o WHERE EXISTS (

   SELECT 1
   FROM customers AS c
   WHERE c.id = o.customer_id
     AND c.active = 1

) ORDER BY o.created_at DESC LIMIT 10;

EXPLAIN REORDER SELECT o.id FROM orders AS o WHERE EXISTS (

   SELECT 1
   FROM customers AS c
   WHERE c.id = o.customer_id
     AND c.active = 1

) ORDER BY o.created_at DESC LIMIT 10;

EXPLAIN PHYSICAL SELECT o.id FROM orders AS o WHERE EXISTS (

   SELECT 1
   FROM customers AS c
   WHERE c.id = o.customer_id
     AND c.active = 1

) ORDER BY o.created_at DESC LIMIT 10;</syntaxhighlight>

The logical output should contain decorrelated stages rather than parser subquery markers. The reordered output explains which relation drives the work. The physical output reveals whether current costs favor direct probes, a prepared membership carrier, an ordered index, or another valid scan source.

Do not copy plan text as a permanent performance guarantee. Validate application-critical queries against the deployed commit, representative cardinalities, current indexes, and realistic parameter values. See Query Planner and Physical Lowering, Data Auto Sharding and Auto Indexing, Performance Measurement, and Supported SQL.