Wikiservice: Refresh MemCP documentation: accuracy, operational guidance, performance profile and maintained API reference

2026-08-28T10:13:54Z

Refresh MemCP documentation: accuracy, operational guidance, performance profile and maintained API reference

← Older revision		Revision as of 12:13, 28 August 2026
Line 11:		Line 11:
	Consider a counter table whose key identifies the event or object being counted:		Consider a counter table whose key identifies the event or object being counted:

	<~~syntaxhighlight lang="sql"~~>CREATE TABLE counters (		<pre>CREATE TABLE counters (
	id INT PRIMARY KEY,		id INT PRIMARY KEY,
	cnt INT NOT NULL		cnt INT NOT NULL
	);		);

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

	The MySQL form can create a missing counter or increment an existing counter in one atomic statement:		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)		<pre>INSERT INTO counters VALUES (2, 1)
	ON DUPLICATE KEY UPDATE cnt = cnt + VALUES(cnt);		ON DUPLICATE KEY UPDATE cnt = cnt + VALUES(cnt);

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

	{\| class="wikitable"		{\| class="wikitable"
Line 41:		Line 41:
	The PostgreSQL-syntax endpoint provides the corresponding <code>ON CONFLICT</code> form:		The PostgreSQL-syntax endpoint provides the corresponding <code>ON CONFLICT</code> form:

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

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

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

	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]].		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]].
Line 58:		Line 58:
	The following query joins customers with their recent orders, aggregates revenue, and ranks the resulting groups:		The following query joins customers with their recent orders, aggregates revenue, and ranks the resulting groups:

	<~~syntaxhighlight lang="sql"~~>SELECT c.id,		<pre>SELECT c.id,
	c.name,		c.name,
	SUM(o.amount) AS total,		SUM(o.amount) AS total,
Line 68:		Line 68:
	HAVING SUM(o.amount) > 100		HAVING SUM(o.amount) > 100
	ORDER BY total DESC		ORDER BY total DESC
	LIMIT 20;</~~syntaxhighlight~~>		LIMIT 20;</pre>

	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 <code>LIMIT</code>. Window expressions whose order-dependent result is reusable can be backed by an ordered-reduce computed column (ORC).		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 <code>LIMIT</code>. Window expressions whose order-dependent result is reusable can be backed by an ordered-reduce computed column (ORC).
Line 78:		Line 78:
	=== IN and EXISTS ===		=== IN and EXISTS ===

	<~~syntaxhighlight lang="sql"~~>SELECT o.id, o.amount		<pre>SELECT o.id, o.amount
	FROM orders AS o		FROM orders AS o
	WHERE o.customer_id IN (		WHERE o.customer_id IN (
Line 93:		Line 93:
	WHERE i.product_id = p.id		WHERE i.product_id = p.id
	AND i.quantity > 0		AND i.quantity > 0
	);</~~syntaxhighlight~~>		);</pre>

	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 <code>NOT IN</code>; a truth-only RecSet must not erase UNKNOWN results.		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 <code>NOT IN</code>; a truth-only RecSet must not erase UNKNOWN results.
Line 99:		Line 99:
	=== Correlated scalar aggregate ===		=== Correlated scalar aggregate ===

	<~~syntaxhighlight lang="sql"~~>SELECT c.id,		<pre>SELECT c.id,
	c.name,		c.name,
	(		(
Line 108:		Line 108:
	FROM customers AS c		FROM customers AS c
	ORDER BY total DESC		ORDER BY total DESC
	LIMIT 20;</~~syntaxhighlight~~>		LIMIT 20;</pre>

	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 <code>customer_id</code>, or a RecSet-based carrier. Identical nested work can be shared instead of being planned independently for every occurrence.		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 <code>customer_id</code>, or a RecSet-based carrier. Identical nested work can be shared instead of being planned independently for every occurrence.
Line 116:		Line 116:
	=== Derived tables and UNION ===		=== Derived tables and UNION ===

	<~~syntaxhighlight lang="sql"~~>SELECT totals.customer_id, totals.total		<pre>SELECT totals.customer_id, totals.total
	FROM (		FROM (
	SELECT customer_id, SUM(amount) AS total		SELECT customer_id, SUM(amount) AS total
Line 128:		Line 128:
	SELECT customer_id, amount FROM current_orders		SELECT customer_id, amount FROM current_orders
	UNION ALL		UNION ALL
	SELECT customer_id, amount FROM archived_orders;</~~syntaxhighlight~~>		SELECT customer_id, amount FROM archived_orders;</pre>

	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.		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.
Line 189:		Line 189:
	Both SQL syntax modes expose the planner phases:		Both SQL syntax modes expose the planner phases:

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

	* <code>EXPLAIN</code> shows the optimized executable Scheme plan.		* <code>EXPLAIN</code> shows the optimized executable Scheme plan.
Line 203:		Line 203:
	For example, compare the phases of a selective ordered membership query:		For example, compare the phases of a selective ordered membership query:

	<~~syntaxhighlight lang="sql"~~>EXPLAIN IR		<pre>EXPLAIN IR
	SELECT o.id		SELECT o.id
	FROM orders AS o		FROM orders AS o
Line 237:		Line 237:
	)		)
	ORDER BY o.created_at DESC		ORDER BY o.created_at DESC
	LIMIT 10;</~~syntaxhighlight~~>		LIMIT 10;</pre>

	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.		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]].		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]].

Wikiservice: Refresh MemCP documentation: accuracy, operational guidance, performance profile and maintained API reference

2026-08-28T09:59:23Z

Refresh MemCP documentation: accuracy, operational guidance, performance profile and maintained API reference

Show changes

Carli at 00:01, 26 January 2026

2026-01-26T00:01:40Z

← Older revision		Revision as of 02:01, 26 January 2026
Line 26:		Line 26:
	'''3 rows in set (0,001 sec)'''		'''3 rows in set (0,001 sec)'''
	This kind of behaviour is atomic, so it is safer than splitting it into two query. This behaviour also scales over multicores via [[Data Auto Sharding and Auto Indexing]]		This kind of behaviour is atomic, so it is safer than splitting it into two query. This behaviour also scales over multicores via [[Data Auto Sharding and Auto Indexing]]

			== Subselects ==
			MemCP supports subselects in expressions including IN, EXISTS, and scalar subqueries.

			=== IN Subselect ===
			<code>SELECT * FROM orders WHERE customer_id IN (SELECT id FROM customers WHERE active = 1)</code>

			=== EXISTS Subselect ===
			<code>SELECT * FROM products WHERE EXISTS (SELECT 1 FROM inventory WHERE product_id = products.id)</code>

			=== Scalar Subselect in SELECT ===
			<code>SELECT id, (SELECT COUNT(*) FROM orders WHERE customer_id = customers.id) AS order_count
			FROM customers</code>

			=== Scalar Subselect with Aggregation ===
			<code>SELECT (SELECT MAX(price) FROM products WHERE category = 'electronics') AS max_price</code>

			=== Correlated Subselects ===
			Subselects can reference columns from the outer query:
			<code>SELECT id, name,
			(SELECT SUM(amount) FROM orders WHERE orders.customer_id = customers.id) AS total
			FROM customers</code>

			=== Derived Tables ===
			<code>SELECT t.* FROM (
			SELECT id, COUNT(*) AS cnt FROM orders GROUP BY id
			) AS t
			WHERE t.cnt > 5</code>

			=== Limitations ===

			* Scalar subselects must return exactly one row (error if multiple rows)
			* Scalar subselects return NULL if no rows match

Carli: /* Atomic Counters through Unique Key Checks */

2024-05-20T10:41:43Z

Atomic Counters through Unique Key Checks

← Older revision		Revision as of 12:41, 20 May 2024
Line 25:		Line 25:
	+------+------+		+------+------+
	'''3 rows in set (0,001 sec)'''		'''3 rows in set (0,001 sec)'''
			This kind of behaviour is atomic, so it is safer than splitting it into two query. This behaviour also scales over multicores via [[Data Auto Sharding and Auto Indexing]]

Carli: Created page with "== Atomic Counters through Unique Key Checks == Imagine the following table: CREATE TABLE u(id INT PRIMARY KEY, cnt INT); INSERT INTO u VALUES (1, 2), (2, 4); SELECT * FROM u; +------+------+ | id | cnt | +------+------+ | 1 | 2 | | 2 | 4 | +------+------+ '''3 rows in set (0,001 sec)''' If you want to add to `cnt` whenever an event occurs, add a unique key over `id` and do the following: INSERT INTO u VALUES (2, 1) O..."

2024-05-20T09:55:02Z

Created page with "== Atomic Counters through Unique Key Checks == Imagine the following table: CREATE TABLE u(id INT PRIMARY KEY, cnt INT); INSERT INTO u VALUES (1, 2), (2, 4); SELECT * FROM u; +------+------+ | id | cnt | +------+------+ | 1 | 2 | | 2 | 4 | +------+------+ '''3 rows in set (0,001 sec)''' If you want to add to <code>cnt</code> whenever an event occurs, add a unique key over <code>id</code> and do the following: INSERT INTO u VALUES (2, 1) O..."

New page

== Atomic Counters through Unique Key Checks ==
Imagine the following table:
CREATE TABLE u(id INT PRIMARY KEY, cnt INT);

INSERT INTO u VALUES (1, 2), (2, 4);

SELECT * FROM u;

+------+------+
| id | cnt |
+------+------+
| 1 | 2 |
| 2 | 4 |
+------+------+
'''3 rows in set (0,001 sec)'''
If you want to add to <code>cnt</code> whenever an event occurs, add a unique key over <code>id</code> and do the following:
INSERT INTO u VALUES (2, 1) ON DUPLICATE KEY UPDATE cnt = cnt + VALUES(cnt);

+------+------+
| id | cnt |
+------+------+
| 1 | 2 |
| 2 | 5 |
+------+------+
'''3 rows in set (0,001 sec)'''

Advanced SQL Tutorial - Revision history

Wikiservice: Refresh MemCP documentation: accuracy, operational guidance, performance profile and maintained API reference

Wikiservice: Refresh MemCP documentation: accuracy, operational guidance, performance profile and maintained API reference

Carli at 00:01, 26 January 2026

Carli: /* Atomic Counters through Unique Key Checks */