The Afternoon We Put Magento's Schema on SQL Server

There is a moment in every LAMP developer's career where the reporting query wins.

You have a Magento store. It runs on MySQL, because Magento runs on MySQL. Everything you know about SQL, you learned inside InnoDB, and InnoDB has been fine. Then merchandising asks for "top five sellers per category, per store view, month over month, with the year-over-year delta," and you write it, and it runs for forty minutes, and it takes the storefront down with it.

I got called into a shop in that exact state in 2016. Magento 1.9, five store views, about 900,000 SKUs, six years of order history. The stack was as classic as it gets: Apache, PHP, MySQL 5.6, one primary and one replica. And in the corner of the rack, doing nothing but eating license fees, was a SQL Server 2012 instance the ERP team had left behind.

I spent an afternoon with one of their developers showing him why we were going to feed Magento's schema into that box, and what changed once we did. This document is that afternoon, written out. The through-line is simple: Magento's schema is the fixed artifact, and the engine underneath it is a variable. Every heading below is a question that comes up in SQL interviews and code reviews, and every one of them has a different answer depending on whether you're standing on InnoDB, SQL Server, or Oracle.

A note on honesty before we start. MySQL 8.0 closed most of the gaps I'm about to describe — window functions, CTEs, hash joins, real CHECK constraints, EXCEPT/INTERSECT, LATERAL. If you're on 8.0 or later today, a lot of this reads as history. It's still worth reading as history, because the reasoning transfers, and because a startling number of production Magento installs are still on 5.7 or on MariaDB, which diverged and does not have all of it either.

---

The setup: why two engines at all

The first thing I did was not write a query. It was draw the schema on a whiteboard in three layers, because until you see the layers, Magento looks arbitrary.

Layer one is truth. catalog_product_entity, catalog_product_entity_varchar and its siblings, eav_attribute, sales_order, sales_order_item, customer_entity. Normalized, constrained, slow to read, correct. This is what you restore from backup.

Layer two is index. catalog_product_index_price, catalog_category_product_index_store1, catalog_product_index_eav, catalogsearch_fulltext_scope1. Every one of these is derived. Drop them all and you lose nothing but the time it takes bin/magento indexer:reindex to rebuild them.

Layer three is presentation. sales_order_grid, customer_grid_flat. Flattened read models, each shaped for exactly one admin screen.

Layer two exists because layer one is EAV. A merchant needs to add a "Shell Fabric Denier" attribute to jackets without a schema migration, so attributes live as rows:

-- catalog_product_entity: one row per product, almost no attributes
entity_id | attribute_set_id | type_id      | sku          | created_at
--------- | ---------------- | ------------ | ------------ | ----------
   88213  |               15 | configurable | ALPINE-PARKA | 2014-09-02

-- catalog_product_entity_varchar: the value, scoped per store view
value_id | attribute_id | store_id | entity_id | value
-------- | ------------ | -------- | --------- | ------------------
 9182734 |           73 |        0 |     88213 | Alpine Down Parka
 9182735 |           73 |        2 |     88213 | Parka Alpin

store_id = 0 is the default scope. store_id = 2 overrides it for the French store view. Remember that shape; it's going to teach us the most important thing about outer joins later.

This design is infinitely flexible and completely unusable for a storefront, which is why Magento built layer two. And here's the part that matters for our story: Magento's entire indexer subsystem — mview_state, the *_cl changelog tables, index invalidation, "Update by Schedule" — is a hand-rolled incremental materialized view engine, written in PHP, because MySQL does not have materialized views. Thousands of lines of framework code implementing something Oracle gives you in one DDL statement.

So the pitch to the developer was this. We are not going to migrate off MySQL. Magento stays where it is. We are going to replicate the interesting tables into SQL Server, and every reporting question that MySQL answers badly, we will answer over there instead — using tools MySQL simply does not have. In exchange, we accept the cost of maintaining a second schema, which is real and which I'll be honest about at the end.

The ETL was unglamorous: a nightly full pull of the dimension-ish tables, incremental pulls on sales_order, sales_order_item, and sales_order_payment keyed on entity_id and updated_at. Nothing clever. The cleverness is all in what you can do once the data lands.

---

Choosing the right query pattern for the problem

The first ticket he handed me was: "Marketing wants the email addresses of everyone who has ever ordered the Alpine Down Parka." He'd already written it.

SELECT DISTINCT c.entity_id, c.email
FROM customer_entity c
JOIN sales_order o      ON o.customer_id = c.entity_id
JOIN sales_order_item i ON i.order_id = o.entity_id
WHERE i.sku = 'ALPINE-PARKA';

Forty-one thousand rows, ninety seconds. The parka had sold 380,000 units. The join fanned out to 380,000 rows and then sorted every one of them to collapse back down to 41,000. He paid for a sort of the fanned-out set and threw the result away.

Before you write anything, answer three questions. I've asked these in code review for fifteen years and I have never regretted it. What is the grain of my result — one row per what? If you can't finish that sentence, you will produce duplicates. Am I filtering, or am I enriching? Filtering means a semi-join; enriching means a real join or a window function. Do I need one value per group, or one row per group? One value is an aggregate; one row is a window function or a lateral.

Here the grain is one row per customer, and we are filtering. We want nothing from the order — just a yes or no. That's EXISTS:

SELECT c.entity_id, c.email
FROM customer_entity c
WHERE EXISTS (
  SELECT 1
  FROM sales_order o
  JOIN sales_order_item i ON i.order_id = o.entity_id
  WHERE o.customer_id = c.entity_id
    AND i.sku = 'ALPINE-PARKA'
);

The optimizer stops at the first matching order per customer. No fan-out, no sort. Two seconds on the same box. This works identically on all four engines and there is nothing platform-specific to learn — which is exactly why I started here. Most bad SQL is not an engine problem. It's a grain error wearing a DISTINCT.

Here's the table I keep pinned above my desk, and it is portable in its entirety:

Question	Right pattern	What people reach for
Customers who ordered ≥1 parka	EXISTS	JOIN + DISTINCT
Products never ordered	NOT EXISTS	NOT IN
Each order plus its customer's email	JOIN	correlated scalar subquery
Revenue per customer	GROUP BY	correlated aggregate in SELECT
Each order plus that customer's lifetime total	window function	correlated aggregate in SELECT
Most recent order per customer, whole row	ROW_NUMBER() or lateral	MAX(created_at) + self-join
Top 5 sellers per category	RANK/DENSE_RANK/ROW_NUMBER	correlated COUNT subquery

Now the first place the second engine earned its keep. On MySQL, when a query got complicated, our only debugging tool was EXPLAIN — an estimate, not a measurement. EXPLAIN ANALYZE didn't arrive until MySQL 8.0.18. On SQL Server we had the actual execution plan with real row counts next to estimated ones, and SET STATISTICS IO, TIME ON telling us exactly how many logical reads each table cost. The developer's reaction to seeing an actual plan for the first time was the reaction everyone has: oh, it was scanning that the whole time.

One structural difference bites people immediately when they port a query, so learn it now. A CTE does not mean the same thing on every engine.

Engine	What a CTE actually does
PostgreSQL ≤ 11	Always materialized. Hard optimization fence — outer predicates do not push in.
PostgreSQL 12+	Inlined if referenced once, non-recursive, non-volatile. Force with AS MATERIALIZED / AS NOT MATERIALIZED.
SQL Server	Effectively always inlined. A CTE referenced three times is executed three times. No hint to change it.
Oracle	Cost-based. /*+ MATERIALIZE */ and /*+ INLINE */ are your controls.
MySQL 8.0+	Materializes derived tables and CTEs in many cases; merge is possible. Weakest at pushing predicates in.

The SQL Server failure is memorable. A tidy CTE that scans sales_order_item once, referenced in three branches of a UNION ALL, scans 40 million rows three times. It looks elegant in review and it is a disaster at runtime. If a CTE is referenced more than once and the underlying scan is expensive, stage it explicitly — SELECT ... INTO #orders_2016 on SQL Server, and stop hoping.

---

Using NOT EXISTS / EXISTS where appropriate

The second ticket was merchandising's: "Give us every product that has never been ordered so we can cull the catalog." He wrote the obvious thing and got zero rows. Every time. On a catalog where you could visibly point at products nobody had ever bought.

SELECT e.entity_id, e.sku
FROM catalog_product_entity e
WHERE e.entity_id NOT IN (SELECT product_id FROM sales_order_item);

Look at Magento's own foreign key on that column and the bug becomes obvious:

CONSTRAINT SALES_ORDER_ITEM_PRODUCT_ID_CATALOG_PRODUCT_ENTITY_ENTITY_ID
  FOREIGN KEY (product_id) REFERENCES catalog_product_entity (entity_id)
  ON DELETE SET NULL

ON DELETE SET NULL. The moment a merchant deletes one product, every historical order line for it gets product_id = NULL. In a six-year-old store, that column is guaranteed to contain NULLs.

Now the semantics. x NOT IN (a, b, NULL) expands to x <> a AND x <> b AND x <> NULL. That last term is UNKNOWN — not false, unknown. A conjunction containing UNKNOWN can never evaluate to TRUE. So the filter matches nothing at all. Not fewer rows. Nothing. Silently, with no error and no warning.

This is ANSI behavior and it is identical on MySQL, PostgreSQL, SQL Server, and Oracle. I want to be emphatic about that, because the developer's first instinct was that MySQL had done something to him. It hadn't. This one is on us, not the engine, and moving to a fancier database does not save you.

-- Correct on every engine, forever
SELECT e.entity_id, e.sku
FROM catalog_product_entity e
WHERE NOT EXISTS (
  SELECT 1 FROM sales_order_item i WHERE i.product_id = e.entity_id
);

NOT EXISTS tests row existence rather than value comparison, so a NULL is simply a non-match. Never write NOT IN against a subquery. Against a short literal list — status NOT IN ('canceled','closed') — it's fine and readable. Against a subquery, the only safe version requires the column to be declared NOT NULL in the DDL, not "we're pretty sure," and even then it's usually the slower plan.

Which brings us to where the engines diverge, and to the first genuinely dramatic difference we measured that afternoon.

Engine	NOT EXISTS	NOT IN against a subquery
Oracle	Anti-join	Handled well — null-aware anti-join (ANTI NA) since 11g. Rough parity.
SQL Server	Anti semi-join	Usually an anti-join, but nullable columns add probes and plans get ugly.
PostgreSQL	Clean anti-join	Falls back to an opaque SubPlan filter — and is not rescued by declaring the columns NOT NULL on released versions.
MySQL 8.0.17+	Anti-join	Antijoin transformation from 8.0.17. Before that: a dependent subquery, evaluated per row.

Read that MySQL row again. On the 5.6 box, NOT EXISTS over a 900,000-row product table against a 12-million-row order item table was a per-row dependent subquery — nine hundred thousand separate probes. It ran for over an hour. The identical statement on SQL Server 2012 became a hash-based anti semi-join and returned in under a minute. Same SQL. Same data. Same index definitions. The difference was entirely in what the optimizer was capable of transforming.

You will also meet a third spelling in legacy Magento code, and now you know why it's there — before 8.0.17 it was the only way to get a decent anti-join plan out of MySQL:

SELECT e.entity_id, e.sku
FROM catalog_product_entity e
LEFT JOIN sales_order_item i ON i.product_id = e.entity_id
WHERE i.item_id IS NULL;

It's semantically safe — no NULL trap — but it's fragile. Add a second predicate on i in the WHERE clause instead of the ON clause and you silently convert the outer join to an inner join and get zero rows again. Recognize it when you inherit it; don't write it new.

And one thing to stop arguing about in review: SELECT 1 versus SELECT * versus SELECT NULL inside EXISTS makes zero difference on any of the four engines. The select list isn't evaluated. What matters is whether the correlation predicate is indexed. Spend the argument there.

---

Avoiding unnecessary nested aggregate subqueries

This is the section where the developer got annoyed with me, and he was right to, because I'd been implying his code was sloppy when actually his engine had left him no choice.

The ticket: "Customer service wants a screen showing each order alongside that customer's lifetime order count, lifetime spend, and first order date." His version:

SELECT o.entity_id, o.increment_id, o.customer_id,
       (SELECT COUNT(*)               FROM sales_order o2 WHERE o2.customer_id = o.customer_id) AS lifetime_orders,
       (SELECT SUM(base_grand_total)  FROM sales_order o3 WHERE o3.customer_id = o.customer_id) AS lifetime_spend,
       (SELECT MIN(created_at)        FROM sales_order o4 WHERE o4.customer_id = o.customer_id) AS first_order_at
FROM sales_order o
WHERE o.created_at >= '2016-01-01';

Three correlated subqueries, which means up to three extra index range scans per output row. At two million output rows that's six million extra scans. On a good day the optimizer decorrelates one. Plan for the bad day.

Here's the thing I owed him an apology for: MySQL 5.7 had no window functions. None. They arrived in 8.0. So the elegant one-pass rewrite I was about to show him did not exist on his production database. The correlated-subquery pattern all over the Magento ecosystem is not laziness. It is what people wrote because the alternative was not available.

On SQL Server 2012 — and on Oracle since 8i, which is to say since 1999 — it was available:

SELECT entity_id, increment_id, customer_id,
       COUNT(*)              OVER (PARTITION BY customer_id) AS lifetime_orders,
       SUM(base_grand_total) OVER (PARTITION BY customer_id) AS lifetime_spend,
       MIN(created_at)       OVER (PARTITION BY customer_id) AS first_order_at
FROM sales_order
WHERE created_at >= '2016-01-01';

One scan, one sort by customer_id, three aggregates computed together. But read the semantics carefully, because this is a trap that ships: the window is now over the filtered set. "Lifetime spend" just quietly became "spend since January." If the business meant lifetime, this is wrong in a way that looks right, and nobody catches it until the numbers get compared against finance.

When you need aggregates over the full history but only rows from a window, pre-aggregate and join:

WITH cust AS (
  SELECT customer_id,
         COUNT(*)              AS lifetime_orders,
         SUM(base_grand_total) AS lifetime_spend,
         MIN(created_at)       AS first_order_at
  FROM sales_order
  GROUP BY customer_id
)
SELECT o.entity_id, o.increment_id, o.customer_id,
       c.lifetime_orders, c.lifetime_spend, c.first_order_at
FROM sales_order o
JOIN cust c ON c.customer_id = o.customer_id
WHERE o.created_at >= '2016-01-01';

One aggregate pass over the child, one hash join. This is the workhorse pattern and it's the one to reach for by default. Aggregate before you join, never after. Joining first fans out rows and then you're summing an inflated set, which is also how you get silently wrong totals — we'll do that failure explicitly in the next section.

The third pattern is the one where SQL Server had something MySQL wouldn't get until 8.0.14. "Show me each customer plus every column of their most recent order" needs a whole row, not an aggregate:

-- SQL Server 2012
SELECT c.entity_id, c.email, lo.increment_id, lo.created_at, lo.base_grand_total
FROM customer_entity c
OUTER APPLY (
    SELECT TOP 1 o.increment_id, o.created_at, o.base_grand_total
    FROM sales_order o
    WHERE o.customer_id = c.entity_id
    ORDER BY o.created_at DESC, o.entity_id DESC
) lo;

PostgreSQL and MySQL 8.0.14+ spell it LEFT JOIN LATERAL (...) ON true; Oracle 12c+ accepts either. The decision rule is worth internalizing because it's counterintuitive: lateral does N cheap index seeks, ROW_NUMBER() does one big sort. For a VIP list of 200 customers against 12 million orders, lateral wins by orders of magnitude. For "top 3 orders per customer across all 700,000 customers," ROW_NUMBER() wins — one sorted pass beats 700,000 seeks. Know your N.

Oracle has a shortcut here with no equivalent on the other three, and if you ever land on an Oracle warehouse it will save you a self-join:

SELECT customer_id,
       MAX(increment_id) KEEP (DENSE_RANK LAST ORDER BY created_at, entity_id) AS last_increment_id,
       SUM(base_grand_total) AS lifetime_spend
FROM sales_order
GROUP BY customer_id;

KEEP (DENSE_RANK FIRST/LAST) gives you "the value of column X from the row with the max of Y" inside a plain GROUP BY. No window pass, no join. PostgreSQL's nearest analogue is DISTINCT ON. SQL Server and MySQL have nothing like it.

And the Magento footnote: the platform already solved this problem, and the solution is called customer_grid_flat. When the same expensive aggregation is demanded by a UI on every page load, the right answer eventually stops being "a better query" and becomes "a maintained table." We'll get there.

---

Understanding joins, filtering, grouping, and set-based logic

Third ticket, and the one that had actually triggered my involvement: "Finance says Q1 revenue on the dashboard doesn't match the accounting system."

-- WRONG
SELECT o.entity_id,
       SUM(i.row_total)   AS item_total,
       SUM(p.amount_paid) AS paid_total
FROM sales_order o
JOIN sales_order_item    i ON i.order_id = o.entity_id
JOIN sales_order_payment p ON p.parent_id = o.entity_id
GROUP BY o.entity_id;

An order with three line items and one payment produces three rows, so SUM(p.amount_paid) is three times too large. Add a split payment and both sums are wrong. The numbers look plausible, which is precisely why this ships to production and survives there for months.

The fix is the rule from the last section, applied: aggregate each branch separately, then join the aggregates.

SELECT o.entity_id, i.item_total, p.paid_total
FROM sales_order o
LEFT JOIN (SELECT order_id, SUM(row_total) AS item_total
           FROM sales_order_item GROUP BY order_id) i ON i.order_id = o.entity_id
LEFT JOIN (SELECT parent_id, SUM(amount_paid) AS paid_total
           FROM sales_order_payment GROUP BY parent_id) p ON p.parent_id = o.entity_id;

Diagnostic trick worth ten seconds of your life: drop COUNT(DISTINCT i.item_id), COUNT(DISTINCT p.entity_id), COUNT(*) into the broken query. When COUNT(*) equals the product of the other two, you've proven the fan-out to yourself and you can stop debating it.

Now, ON versus WHERE, which Magento's store-scope model teaches better than any textbook example. Remember store_id = 0 for the default and store_id = 2 for the French override:

SELECT e.entity_id,
       COALESCE(store_val.value, default_val.value) AS product_name
FROM catalog_product_entity e
JOIN catalog_product_entity_varchar default_val
  ON default_val.entity_id = e.entity_id
 AND default_val.attribute_id = 73
 AND default_val.store_id = 0
LEFT JOIN catalog_product_entity_varchar store_val
  ON store_val.entity_id = e.entity_id
 AND store_val.attribute_id = 73
 AND store_val.store_id = 2;

Move AND store_val.store_id = 2 from the ON clause into WHERE and you have silently converted the LEFT JOIN into an INNER JOIN. Your product list collapses from 900,000 rows to the 8,000 products that happen to have a French override. Any predicate on the null-supplying side of an outer join, placed in WHERE, eliminates the NULL-extended rows. The only WHERE predicate that belongs there is IS NULL, when you're deliberately writing an anti-join.

Sargability next, because two of our slowest reports were slow for this reason alone. Wrapping an indexed column in a function kills the index on every engine:

WHERE YEAR(o.created_at) = 2016         -- no index use
WHERE DATE(o.created_at) = '2016-03-01' -- no index use
WHERE UPPER(c.email) = '[e-mail]'        -- no index use

The remedies diverge. PostgreSQL and Oracle give you real expression and function-based indexes. MySQL didn't get functional indexes until 8.0.13 — before that you added a generated column and indexed that. SQL Server has no true expression index either; you add a computed column and index it, persisting it unless you're certain the expression is deterministic and precise. And two date traps that will bite you specifically: Oracle's DATE always carries a time component, so WHERE created_at = DATE '2016-07-27' misses everything; and SQL Server's legacy datetime has ~3.33ms precision and rounds, so BETWEEN '2016-03-01' AND '2016-03-31 23:59:59.999' rounds up to April 1 and double-counts a day. Half-open ranges — >= start AND < end — are correct on all four. Just make it the habit.

Implicit conversion deserves its own paragraph because it burned us on the SQL Server side within a week of go-live. Magento's sales_order.increment_id is varchar(32), holding values like '100000123'. Someone writes WHERE o.increment_id = 100000123 with a number, and MySQL converts the column to a number to compare — full scan of twelve million rows. Oracle does the same thing with a VARCHAR2 column against a numeric literal. On SQL Server the shape is different and sneakier: an ORM sending NVARCHAR parameters against a VARCHAR column forces CONVERT_IMPLICIT on the column, because NVARCHAR wins the data-type precedence fight. That single mismatch is the number one cause of "it's fast in SSMS but slow from the app," and I have watched entire consulting engagements resolve to it.

Set operators are where MySQL's era shows most starkly. EXCEPT and INTERSECT did not arrive in MySQL until 8.0.31. On the 5.6 box, "products in the catalog but not in the ERP feed" was hand-written as NOT EXISTS. On SQL Server it was one line. And whichever engine you're on, ask the question that actually costs money:

-- These are not the same
SELECT ... FROM sales_order WHERE store_id = 1
UNION      -- full dedupe: sort or hash the entire combined result
SELECT ... FROM sales_order WHERE store_id = 2;

SELECT ... FROM sales_order WHERE store_id = 1
UNION ALL  -- free, and provably correct here since the branches are disjoint
SELECT ... FROM sales_order WHERE store_id = 2;

Always use UNION ALL unless dedupe is genuinely required. On a 200-million-row order-history report that is the difference between forty seconds and eight minutes. And note that EXCEPT/MINUS deduplicate while NOT EXISTS does not — they are not interchangeable when duplicates carry meaning.

The last piece is the one that justified the whole project, and it's about join algorithms:

Algorithm	PostgreSQL	MySQL 5.7	MySQL 8.0	SQL Server	Oracle
Nested loop	✅	✅	✅	✅	✅
Hash join	✅	❌	✅ (8.0.18+)	✅	✅
Merge join	✅	❌	❌ never	✅	✅

MySQL 5.7 had only nested-loop variants. A six-way analytical join across orders, items, products, categories, customers, and store views — the kind of thing merchandising asks for casually — is resolved by SQL Server or Oracle with a couple of hash joins in seconds, and by MySQL 5.7 by looping. Ours ran for over three hours before we killed it. That query was the reason the SQL Server box stopped being idle. And note that even MySQL 8.0 still has no merge join, so pre-sorted large-to-large joins never get that option. MySQL is a weaker analytical engine than the other three, and Magento's architecture already concedes this — that's what layer two is for.

One last MySQL-specific correctness item, since it caused an upgrade scare: ONLY_FULL_GROUP_BY has been on by default since 5.7.5, and a lot of Magento extensions broke when it landed. Many shops "fixed" it by disabling the mode. Don't. Code that runs with it off returns an arbitrary value from an arbitrary row in each group. That's a correctness bug that produces plausible numbers, which is the worst kind.

---

Using window functions like DENSE_RANK() and similar tools when appropriate

Now the fun part. The report that started everything: "Top five sellers per category, per store view."

On MySQL 5.7 this had been implemented as a correlated COUNT subquery — for each product, count how many products in the same category sold more than it, and keep the ones where that count is under five. It worked, in the sense that it eventually returned. On SQL Server it became this:

WITH sales AS (
  SELECT ccp.category_id,
         i.product_id,
         SUM(i.qty_ordered) AS units
  FROM sales_order_item i
  JOIN sales_order o                ON o.entity_id = i.order_id
  JOIN catalog_category_product ccp ON ccp.product_id = i.product_id
  WHERE o.created_at >= '2016-01-01'
    AND o.status = 'complete'
  GROUP BY ccp.category_id, i.product_id
),
ranked AS (
  SELECT category_id, product_id, units,
         DENSE_RANK() OVER (PARTITION BY category_id
                            ORDER BY units DESC, product_id ASC) AS rnk
  FROM sales
)
SELECT * FROM ranked WHERE rnk <= 5;

Two things in there are deliberate, and both cause production tickets when omitted.

The first is the choice of ranking function, and it is a business decision, not a technical one. For units of 100, 100, 90, 80: ROW_NUMBER() returns 1, 2, 3, 4 — exactly one row per position, ties broken arbitrarily. RANK() returns 1, 1, 3, 4 — competition ranking, with gaps after ties. DENSE_RANK() returns 1, 1, 2, 3 — no gaps, which is what you want for "top five distinct performance tiers." So: if two products tie for fifth place, does the homepage show five products or six? ROW_NUMBER shows five and drops one at random, which is nondeterministic and a real bug. DENSE_RANK shows six. Ask merchandising, then write down which one you were told.

The second is , product_id ASC in the window's ORDER BY. Without it, a category where four products all sold 240 units produces a different top five on every execution. Then someone files "the homepage changed but we didn't change anything," and you spend a day proving the data is identical. Every window ORDER BY gets a deterministic tiebreaker. This single habit eliminates an entire genre of ticket.

The next request was a running revenue total by day, and it produced the most instructive bug of the whole engagement:

-- WRONG in a way that looks completely right
SELECT o.created_at, o.base_grand_total,
       SUM(o.base_grand_total) OVER (ORDER BY o.created_at) AS running_total
FROM sales_order o;

When you write OVER (ORDER BY y) with no frame clause, the implicit frame is RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW. And RANGE includes all peer rows — every row tied on y — at once. Orders share timestamps constantly. So every order placed on March 3rd got the full March 3rd total instead of a row-by-row running sum, and the chart came out as a staircase. Finance noticed four months later.

-- What you almost always meant
SUM(o.base_grand_total) OVER (
  ORDER BY o.created_at, o.entity_id
  ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
)

There is a second, entirely separate reason to write ROWS explicitly, and it's SQL Server–specific: the default RANGE frame uses an on-disk worktable spool, while ROWS uses an in-memory spool. On large partitions, specifying ROWS is routinely a five-to-ten-times improvement even when the results would be identical. Make it mandatory in SQL Server code review. Related trap on all engines: LAST_VALUE(x) OVER (ORDER BY y) returns the current row's value, because the default frame ends at the current row. Use ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING, or reverse the sort and use FIRST_VALUE.

LAG and LEAD opened up analysis they'd never attempted, because expressing "days since this customer's previous order" in MySQL 5.7 required a self-join with a correlated MAX, and nobody wanted to maintain it:

SELECT customer_id, entity_id, created_at,
       LAG(created_at) OVER (PARTITION BY customer_id
                             ORDER BY created_at, entity_id) AS prev_order_at,
       DATEDIFF(day,
                LAG(created_at) OVER (PARTITION BY customer_id
                                      ORDER BY created_at, entity_id),
                created_at) AS days_since_prev
FROM sales_order
WHERE status = 'complete';

That query, wrapped in a percentile, became their repeat-purchase-window model and drove the email cadence for two years.

One idiom you will write weekly — deduplication — and one limitation that surprises everyone. Because window functions are evaluated after WHERE, you cannot filter on one in the WHERE clause. You need a wrapper:

WITH ranked AS (
  SELECT v.*,
         ROW_NUMBER() OVER (PARTITION BY v.entity_id, v.attribute_id
                            ORDER BY CASE WHEN v.store_id = 2 THEN 0 ELSE 1 END,
                                     v.value_id DESC) AS rn
  FROM catalog_product_entity_varchar v
  WHERE v.store_id IN (0, 2)
)
SELECT * FROM ranked WHERE rn = 1;

None of PostgreSQL, MySQL, SQL Server, or Oracle support QUALIFY — that's Teradata, Snowflake, and DuckDB. Expect to write the CTE every time. (Re-check that each major release; it's exactly the kind of sugar that ships quietly.) PostgreSQL does give you DISTINCT ON as a shorthand for the rn = 1 case, and it's often faster there.

Availability, since this is the most version-gated area in SQL:

Engine	Basic windows	Full frames	GROUPS/EXCLUDE	Named windows
Oracle	8i (1999)	✅	GROUPS in 21c+	✅
SQL Server	2005 (ranking only)	2012+	❌	❌
PostgreSQL	8.4	✅	✅ (11+)	✅
MySQL	8.0 only	✅	❌	✅

Two performance notes to carry. An index on (partition_cols, order_cols) lets PostgreSQL, Oracle, and SQL Server skip the sort entirely — on a reporting table this is frequently the highest-leverage index you will ever create. And MySQL sorts once per distinct window specification, so three windows with three different ORDER BY clauses means three sorts; consolidate them.

Finally, know when not to use one. If you only need aggregates at the group grain, GROUP BY is cheaper — it collapses rows, while windows preserve them. SELECT DISTINCT customer_id, SUM(x) OVER (PARTITION BY customer_id) is a GROUP BY that got lost on the way to work.

---

Working with JSON data in the database

The fraud team's ticket: "All orders in the last ninety days paid through gateway X, filtered by token prefix."

He went looking and found sales_order_payment.additional_information. Here is the first lesson, and most JSON guides skip it: it isn't a JSON column. It's a text column containing a serialized JSON document. Same for sales_order_item.product_options and quote_item_option.value. Twelve million rows, no index, no validation, no schema, no type checking.

Before any syntax, the design rule. JSON columns are for genuinely schemaless, sparse, or third-party-shaped payloads: gateway responses, webhook bodies, per-tenant custom fields, audit snapshots. The test is simple — if you filter, join, sort, or aggregate on it regularly, it should be a column. Every engine's JSON indexing story is worse than a plain B-tree on a scalar, and you also give up NOT NULL, foreign keys, and check constraints.

Magento actually made this call correctly, and you can see it in the schema: sales_order_grid has a real payment_method column. It didn't have to — the data is right there in additional_information — but the admin grid filters on it, so it became a column in the read model. That's the rule applied by people who had to live with the consequences.

The second rule, which I'll state harshly because I've cleaned up after it three times: do not use JSON as an EAV escape hatch. "Just put the extra attributes in a JSON blob" is how you get a 400GB table nobody understands where every query is a full scan. Magento's EAV is at least indexed and introspectable. A blob is not.

On MySQL 5.6 there was no help at all — JSON functions arrived in 5.7, and even then, there is no direct index on a JSON path. You must materialize the value first:

-- MySQL 5.7: generated column, then index it
ALTER TABLE sales_order_payment
  ADD COLUMN gateway VARCHAR(50)
    AS (JSON_UNQUOTE(JSON_EXTRACT(additional_information, '$.method_title'))) STORED,
  ADD INDEX idx_gateway (gateway);

-- MySQL 8.0.13+: functional index, same thing, less clutter
ALTER TABLE sales_order_payment
  ADD INDEX idx_gateway ((CAST(additional_information->>'$.method_title' AS CHAR(50))));

And there's an operational gotcha that isn't obvious: partial in-place JSON updates only happen under narrow conditions. Otherwise MySQL rewrites the entire document and logs the whole thing to the binlog, and your read replicas fall behind. I have seen replication lag take out a storefront's search because someone added a JSON audit trail to order saves.

On SQL Server the pattern we actually shipped was to shred at load time rather than query in place, which is the right answer on any engine. OPENJSON with an explicit WITH clause is dramatically faster than the default key/value form, and it hands you typed columns:

-- Shred once, during ETL, into a real relational table
INSERT INTO dw.OrderPayment (OrderId, MethodTitle, CcType, CcLast4, AmountPaid)
SELECT p.parent_id, j.method_title, j.cc_type, j.cc_last4, p.amount_paid
FROM stg.sales_order_payment p
CROSS APPLY OPENJSON(p.additional_information)
  WITH (
    method_title VARCHAR(64)  '$.method_title',
    cc_type      VARCHAR(16)  '$.cc_type',
    cc_last4     VARCHAR(4)   '$.cc_last4'
  ) j;

Now the fraud query is a seek against an indexed VARCHAR column, and the JSON never appears in a WHERE clause again. If you must keep JSON in place on SQL Server 2016–2022, it's NVARCHAR(MAX) plus a persisted computed column plus an index, and you should always add CHECK (ISJSON(Payload) = 1) or you will accumulate malformed rows. SQL Server 2025 finally added a native binary json type and a JSON index, but check the fine print before you rely on it — as of writing, the type is GA on Azure SQL and in preview on-prem, and creating a JSON index takes a schema-modification lock for the entire operation with no ONLINE option, which on a twelve-million-row table means a maintenance window.

For completeness, the capability landscape, because if you land on PostgreSQL or Oracle the answer is much better:

	PostgreSQL	MySQL 8.0	SQL Server	Oracle
Native type	json / jsonb (binary)	JSON (binary); MariaDB: LONGTEXT alias	NVARCHAR(MAX); native in 2025	JSON in 21c+
Containment test	@> (indexed)	JSON_CONTAINS	JSON_PATH_EXISTS (2022+)	JSON_EXISTS
Shred to rows	jsonb_array_elements, JSON_TABLE (17+)	JSON_TABLE (8.0.4+)	OPENJSON	JSON_TABLE
General index	GIN on jsonb	❌	JSON index (2025, preview)	CREATE SEARCH INDEX … FOR JSON

PostgreSQL's GIN index on jsonb and Oracle's JSON search index are the only two that index the whole document generically — everywhere else you're indexing one path at a time. Watch out for TOAST on PostgreSQL, though: pulling one small field out of a 2KB document across millions of rows means detoast-and-decompress per row, and it's far slower than people expect. And note MariaDB is not MySQL here at all — its JSON is an alias for LONGTEXT with a validity check, with no binary storage and no multi-valued indexes.

Portable advice: treat JSON as an opaque payload. Store it, retrieve it whole, parse it in the application. The moment you query inside it, you've committed to that engine. Extract the fields you query into real columns at write time — which is, once again, exactly what sales_order_grid does.

---

Indexing strategy

The layered-navigation ticket — "filtering the Jackets category takes eleven seconds" — is the best indexing lesson in the Magento schema, because the answer is not "add an index."

Look at an EAV value table (2.x, roughly — always confirm against your own SHOW CREATE TABLE and the module's db_schema.xml):

CREATE TABLE catalog_product_entity_varchar (
  value_id     int NOT NULL AUTO_INCREMENT,
  attribute_id smallint unsigned NOT NULL,
  store_id     smallint unsigned NOT NULL,
  entity_id    int unsigned NOT NULL,
  value        varchar(255),
  PRIMARY KEY (value_id),
  UNIQUE KEY (entity_id, attribute_id, store_id),
  KEY (attribute_id),
  KEY (store_id)
);

That unique key is beautifully designed for one question: give me every attribute of product 88213. Leading column entity_id, index seek, done — that's the product detail page. Now ask layered navigation's question: give me every product where attribute 167 equals 'Blue'. Your predicate is on attribute_id and value. The composite index leads with entity_id, so it's useless. The standalone KEY (attribute_id) hands you every row for that attribute across the entire catalog and then filters value by hand.

The leading-column rule: an index on (a, b, c) serves predicates on a, (a,b), and (a,b,c). It does not efficiently serve b alone. All four engines can fall back to a full index scan, but that's not what you designed. When two hot access paths need opposite column orders, you need two structures.

On SQL Server we solved it with an index. On MySQL, Magento solved it with a whole table:

-- SQL Server: build the reverse-ordered index, with INCLUDE for coverage
CREATE INDEX IX_ProductVarchar_Attr_Value
  ON dw.CatalogProductEntityVarchar (attribute_id, store_id, value)
  INCLUDE (entity_id);

That INCLUDE clause is worth its own paragraph. Included columns live in the index leaf but not the key, so they don't widen the seek structure and they don't affect ordering, but they make the index covering — the engine never touches the base table. MySQL has no INCLUDE. Neither does Oracle. On both, extra columns must become key columns, which widens every level of the B-tree. PostgreSQL added INCLUDE in version 11. This is a small syntax difference with a large physical consequence.

Magento's equivalent is catalog_product_index_eav, keyed roughly (attribute_id, store_id, value, entity_id) — exactly the reversed column order, built as a separate physical table because that was the tool available. Same idea, ten thousand times the machinery.

Two more universal principles before the divergences. Equality columns first, then range, then include-only: for WHERE store_id = ? AND status = ? AND created_at > ?, index (store_id, status, created_at). Put created_at before status and the index can only seek to the range start and must filter everything after. And every index is a tax on writes — ten indexes means ten structures maintained per insert. Magento's synchronous sales_order_grid writes are a known Black Friday bottleneck for exactly this reason, which is why dev/grid/async_indexing exists.

Now the structural difference that shapes your primary key everywhere:

Engine	Table storage	Consequence
MySQL/InnoDB	Always clustered on the PK. Secondary indexes store the PK value as the row pointer.	A wide PK is duplicated into every secondary index. Random PKs split pages on every insert.
SQL Server	Clustered index optional; without one it's a heap.	Same amplification when clustered. Heaps accumulate forwarded records.
PostgreSQL	Always a heap. CLUSTER is a one-time reorg that doesn't hold.	Physical order drifts; correlation drives range-scan cost.
Oracle	Heap by default; Index-Organized Tables available.	IOTs are excellent for narrow lookup tables, poor for wide ones.

Magento gets this right, and it's worth noticing: every entity PK is a narrow, monotonic auto-increment integer. Not a UUID. On InnoDB, a random UUIDv4 clustered primary key is a documented performance disaster — insert throughput collapses as the B-tree fragments, and every secondary index carries 16 to 36 bytes per entry. If you need distributed IDs, use a time-ordered variant (UUIDv7, NEWSEQUENTIALID(), ULID) stored as BINARY(16) or uniqueidentifier, never as a 36-character string.

Two things bit us during the migration and will bite you. First, InnoDB automatically creates an index on foreign key columns; PostgreSQL, SQL Server, and Oracle do not. MySQL had been doing it for these developers their entire careers, so the moment the schema landed on SQL Server, the "get all items for this order" query fell off a cliff. Unindexed FKs also make parent deletes scan the child table. On Oracle it's worse still — an unindexed FK makes the engine take a share lock on the child table during parent deletes and PK updates, which since 9i is acquired and released per statement rather than held for the transaction, but is still a blocking window on top of a full scan. Every FK column gets an index, on every engine.

Second, filtered indexes. MySQL has no partial index of any kind:

-- SQL Server: 14,000 entries instead of 12,000,000
CREATE INDEX IX_Orders_Pending ON dw.SalesOrder (CreatedAt)
WHERE Status = 'pending_payment';

PostgreSQL spells it the same way with WHERE. MySQL's standard workaround is a separate work-queue table that rows are deleted from once processed — and that is precisely what Magento's *_cl changelog tables are. A partial index, implemented as a table, because the engine doesn't offer one. (One SQL Server gotcha: the optimizer only uses a filtered index when the parameter is provably inside the filter, so WHERE Status = @s often won't use it. OPTION (RECOMPILE) or a literal fixes it, and this surprises people constantly.)

Last, plan stability, because "it was fast yesterday" has a different cause on each engine. On SQL Server it's parameter sniffing — the first execution's parameter values shape the cached plan for everyone, so a plan built for a tiny store view then serves the huge one; remedies are OPTION (RECOMPILE), OPTIMIZE FOR, and Query Store forced plans. On PostgreSQL, prepared statements switch to a generic plan after five executions, and skewed data plus a generic plan is a bad plan. On MySQL the problem is upstream of all that: InnoDB index statistics are sampled, and on a sixty-million-row EAV table the defaults are wildly wrong. Raise innodb_stats_persistent_sample_pages on your big tables and re-run ANALYZE TABLE. That one setting bought us a measurable improvement on the Magento side without touching a query.

---

Normalization vs selective denormalization

Here's where the second engine stopped being a convenience and became an argument about architecture.

Start from the default position: normalize to 3NF and stay there unless you have a measured reason not to. Normalization gives you one place to update each fact, constraints that actually work, narrower rows (more rows per page, better cache hit rate), and a schema that survives requirement changes. Denormalization is a performance optimization with a correctness cost — you're trading guaranteed consistency for speed, and like every optimization you measure first and plan the maintenance.

The distinction that matters more than any other is derived versus duplicated. Derived data can be recomputed from source — a running total, an order count, a price index. If it drifts, you rebuild it, and the risk is manageable. Duplicated data has no single source of truth once both copies are editable, and that is where corruption lives. Denormalize derived data freely, provided you build the rebuild path. Duplicate raw data only with a very clear write-path story.

Magento is a catalogue of both, and reading it this way makes the schema legible. catalog_product_index_price is derived — a precomputed final price per product, per customer group, per website, fully rebuildable. sales_order_grid and customer_grid_flat are derived, and notice they exist per UI screen rather than as one big denormalized table; the shape follows the access path. catalog_category_entity.children_count is a counter cache. And catalog_product_flat_* is the cautionary tale — Adobe has discouraged flat catalog since 2.3 because the write amplification and reindex cost outgrew the read benefit. Denormalization has an expiry date. Revisit yours.

Now the engine gap, which is the largest of any topic in this document:

Engine	Mechanism	Notes
Oracle	Materialized views with query rewrite, FAST REFRESH ON COMMIT, MV logs	Best in class. The optimizer transparently rewrites queries to use the MV — the application never changes.
SQL Server	Indexed views (WITH SCHEMABINDING + unique clustered index)	Maintained synchronously, always correct. Auto-matched by the optimizer in Enterprise; elsewhere you need NOEXPAND.
PostgreSQL	Materialized views, manual REFRESH	No incremental refresh, no automatic rewrite.
MySQL / MariaDB	Nothing.	Build summary tables yourself. Plan for it from day one.

Which brings us to the punchline of the entire afternoon. Magento's indexer subsystem — mview_state, catalog_product_price_cl, catalog_category_product_cl, invalidation flags, "Update on Save" versus "Update by Schedule," bin/magento indexer:reindex — is a hand-rolled incremental materialized view engine, written in PHP, because MySQL doesn't have one. On SQL Server, the daily revenue rollup that had been a cron job, a summary table, and about four hundred lines of PHP became this:

CREATE VIEW dw.vDailyRevenue WITH SCHEMABINDING AS
SELECT o.store_id,
       CAST(o.created_at AS date) AS order_date,
       COUNT_BIG(*)               AS order_count,
       SUM(o.base_grand_total)    AS revenue
FROM dbo.SalesOrder o
WHERE o.status = 'complete'
GROUP BY o.store_id, CAST(o.created_at AS date);
GO

CREATE UNIQUE CLUSTERED INDEX IX_vDailyRevenue
  ON dw.vDailyRevenue (store_id, order_date);

The engine now maintains that aggregate synchronously, transactionally, on every write. It cannot drift, because drift is not representable. There is no cron job to monitor, no changelog table to drain, no "the indexer is stuck" ticket at 2am. The restrictions are real and you should know them going in — SCHEMABINDING required, no outer joins, no subqueries, COUNT_BIG(*) instead of COUNT(*), and SUM requires a COUNT_BIG column so the engine can maintain it incrementally — but within those bounds it is the closest thing to free correctness in this entire article.

Oracle goes one step further, and it's the step that matters most for a legacy application you can't change. With query rewrite enabled, you create the materialized view and then do not touch the application; the optimizer silently redirects qualifying queries to the MV:

CREATE MATERIALIZED VIEW mv_daily_revenue
  BUILD IMMEDIATE
  REFRESH FAST ON COMMIT
  ENABLE QUERY REWRITE
AS SELECT store_id, TRUNC(created_at) AS order_date,
          COUNT(*) AS order_count, SUM(base_grand_total) AS revenue
   FROM sales_order WHERE status = 'complete'
   GROUP BY store_id, TRUNC(created_at);

The design consequence, stated as sharply as I can: the same denormalization is far cheaper on Oracle than on MySQL. On Oracle you can denormalize invisibly and keep the logical model clean. On MySQL every denormalization is explicit, hand-maintained, and a permanent obligation. Weight that when you choose a stack, and don't pretend the obligation isn't there.

Whatever engine you land on, there are three ways to keep redundant data honest, in order of preference: a constraint if the engine can enforce it declaratively, a trigger if it can't (synchronous and correct, but it adds latency to every write and gets bypassed by bulk loads), and a batch reconciliation job that recomputes and reports drift. Build the third one regardless of whether you have the first two. A denormalization without a drift-detection job is a scheduled incident. Magento gives you indexer:reindex as the repair path; it does not give you the detection. Write that yourself.

---

When some redundancy improves performance

His next question was the sharp one: "Then why does sales_order_item store the product name and SKU? Isn't that exactly the duplication you just warned about?"

No. And this is the distinction I most want you to leave with. sales_order_item.name, .sku, and .price are neither derived nor duplicated — they are immutable historical snapshots. sales_order_item.price is not a stale copy of the product's current price. It is a different fact — what we charged this customer on this date — captured at a different point in time. Joining to catalog_product_entity to get a historical price is a bug, not an optimization, and in some jurisdictions it's a compliance failure.

Snapshot anything that appears on a legal or financial document. Order lines, invoice lines, tax rates applied, shipping rates quoted, the customer's address as it was at the time. That rule alone will save you a reconciliation project.

The second category worth the redundancy is the counter cache: post.comment_count instead of COUNT(*) on every page load, or Magento's catalog_category_entity.children_count. Worth it when reads vastly outnumber writes. The gotcha at scale is that every child insert updates the same parent row, so a hot node becomes a lock convoy — and on InnoDB, with Repeatable Read and gap locks, that shows up as deadlocks rather than just slowness. Mitigations are sharded counters (N rows per parent, summed on read), asynchronous batch updates, or accepting eventual consistency with a nightly recompute.

The third and highest-value category is the precomputed aggregate, which we covered above. If you take one denormalization pattern into your career, make it this one. Juniors underuse it more than any other technique, and it is almost always the right answer for reporting over a large OLTP table.

The fourth is worth calling out because it looks wrong and isn't: flattening a hot lookup to avoid a two-hop join. Carrying store_id on every table in a multi-store system, even where it's derivable through a parent, so that every index can lead with it and every query can filter on it directly. That's not a shortcut, it's standard multi-tenant design, and it's what makes row-level security work cleanly later.

And the fifth is materialized path for deep hierarchies, which Magento does in catalog_category_entity:

-- path = '1/2/13/47', alongside parent_id, level, children_count
SELECT * FROM catalog_category_entity WHERE path LIKE '1/2/13/%';

One indexed range scan instead of a recursion. Recursive CTEs are available on all four engines now — MySQL from 8.0, MariaDB from 10.2 — but for a category tree read thousands of times a second on every page render, a prefix LIKE on an indexed column wins every time. Store the path alongside the parent FK: the FK is truth, the path is derived, and you rebuild the path if it drifts.

Here's the redundancy the second engine gave us for free, and it's the one the developer was most excited about. They had a hand-rolled sales_order_audit table maintained by PHP observers, which was incomplete, which everyone knew, and which nobody wanted to fix. On SQL Server 2016+ that entire mechanism is a table option:

ALTER TABLE dw.SalesOrder
  ADD ValidFrom datetime2 GENERATED ALWAYS AS ROW START HIDDEN
        CONSTRAINT DF_ValidFrom DEFAULT SYSUTCDATETIME(),
      ValidTo   datetime2 GENERATED ALWAYS AS ROW END   HIDDEN
        CONSTRAINT DF_ValidTo   DEFAULT '9999-12-31 23:59:59.9999999',
      PERIOD FOR SYSTEM_TIME (ValidFrom, ValidTo);

ALTER TABLE dw.SalesOrder
  SET (SYSTEM_VERSIONING = ON (HISTORY_TABLE = dw.SalesOrderHistory));

-- and then, for free:
SELECT * FROM dw.SalesOrder
FOR SYSTEM_TIME AS OF '2016-11-25 09:00:00'
WHERE entity_id = 4471102;

That's redundancy — a full second copy of every changed row — maintained by the engine, transactionally, with no application code and no possibility of an observer being bypassed. Oracle's Flashback Query gives you a comparable capability from a different direction. MySQL has no equivalent; MariaDB does, via system-versioned tables, which is one of the places MariaDB is ahead of MySQL rather than behind it.

---

Parent/child and subtype/subclass-style table design

Magento has simple, configurable, bundle, grouped, virtual, and downloadable products. They share a SKU, a name, a status, a set of websites, a URL key. They diverge completely in everything else — a bundle needs options and selections, a configurable needs varying attributes and child links, a downloadable needs files and purchase records.

This is the subtype problem, and there are exactly three answers.

Single Table Inheritance puts everything in one table with a discriminator column. Simplest queries, no joins, easy polymorphic lookup — but wide sparse rows, NOT NULL becomes impossible on subtype attributes, and it degrades badly past about four subtypes. Class Table Inheritance puts common attributes in a parent and specifics in child tables sharing the primary key. You get proper NOT NULL and a clean model, at the cost of a join on every full read. Concrete Table Inheritance gives each subtype its own independent table with no parent — fastest per-subtype queries, but no shared key space, no FK can point at "any product," and every polymorphic query needs UNION ALL.

For most business domains, Class Table Inheritance is the default correct answer. Magento chose a hybrid, which is what large real systems usually do: catalog_product_entity.type_id is an STI discriminator, EAV attribute sets play the role of the sparse subtype columns, and genuinely structural subtype data lives in satellite tables — catalog_product_super_attribute and catalog_product_super_link for configurables, catalog_product_bundle_option and _selection for bundles, downloadable_link for downloadables.

Now here is the pattern I most want juniors to learn, and the reason it belongs in a story about a second engine. If you're building greenfield, you can make subtype exclusivity declaratively enforced rather than hoped for:

CREATE TABLE product (
  product_id   BIGINT PRIMARY KEY,
  product_type VARCHAR(20) NOT NULL,
  sku          VARCHAR(64) NOT NULL UNIQUE,
  CONSTRAINT ck_type CHECK (product_type IN ('SIMPLE','BUNDLE','CONFIGURABLE')),
  CONSTRAINT uq_product_type UNIQUE (product_id, product_type)   -- the trick
);

CREATE TABLE bundle_product (
  product_id   BIGINT PRIMARY KEY,
  product_type VARCHAR(20) NOT NULL DEFAULT 'BUNDLE',
  ship_type    VARCHAR(20) NOT NULL,
  CONSTRAINT ck_bundle_type CHECK (product_type = 'BUNDLE'),
  CONSTRAINT fk_bundle_product FOREIGN KEY (product_id, product_type)
    REFERENCES product (product_id, product_type)
);

The redundant product_type column, plus the composite unique key on the parent, plus the composite FK, plus the check constraint on the child, makes it structurally impossible for a row typed SIMPLE to have a bundle_product row. Most teams implement this in application code and get it wrong.

And this is the sharpest single illustration of why the engine underneath you is not a detail: MySQL parsed and silently ignored CHECK constraints until 8.0.16. That pattern did not merely underperform on the LAMP box — it did not function. You could write the DDL, MySQL would accept it without complaint, and it would enforce nothing. On SQL Server and Oracle it had worked for decades. If you inherit a MySQL schema older than 8.0.16, assume every check constraint in it is decorative until you prove otherwise.

Magento also gives you a self-referencing parent/child in sales_order_item.parent_item_id. A configurable product's order line has a child line for the simple SKU that actually shipped; a bundle has one child per selection. Anyone summing row_total across all order items without accounting for parent and child rows double-counts, and that's a Magento reporting bug common enough to have its own genre of Stack Overflow question.

Finally, the anti-pattern, live in production, in a schema millions of stores depend on:

CREATE TABLE url_rewrite (
  url_rewrite_id BIGINT PRIMARY KEY,
  entity_type    VARCHAR(32),   -- 'product' | 'category' | 'cms-page'
  entity_id      BIGINT,        -- points into one of three different tables
  request_path   VARCHAR(255),
  target_path    VARCHAR(255),
  store_id       SMALLINT
);

A polymorphic foreign key. You cannot declare a real FK, so referential integrity is gone, joins need CASE or UNION, and indexes are less selective. The consequence is a well-known Magento pathology: delete a product and its rewrites become orphans, because there's no cascade to fire. Stores accumulate millions of dead url_rewrite rows and a scan on 404 handling. Magento did it because one rewrite table serving every entity type is genuinely convenient and rewrites are looked up by request_path rather than by the FK — a defensible trade with a real cost the community has paid for a decade. If you're designing fresh, use an exclusive arc (one nullable FK per target plus a check that exactly one is non-null) or a shared supertype table that each concrete type references. Both are more work up front and correct forever.

---

Understanding how schema choices affect performance and maintainability

The last hour we spent on the decisions that are cheap on day one and expensive on day one thousand.

Types. Pick the narrowest type that holds the domain, and pick it correctly the first time — changing a primary key from INT to BIGINT on a sixty-million-row EAV table is a multi-hour maintenance window on every one of these engines. If a table could ever exceed 2.1 billion rows, use BIGINT from the start; four bytes costs less than the migration. Money is DECIMAL/NUMBER, always — Magento uses decimal(20,4) for base_grand_total — and never FLOAT or DOUBLE, because 0.1 + 0.2 <> 0.3 in binary floating point and financial reconciliation will find it on the worst possible day. Booleans diverge more than you'd expect: PostgreSQL has a real one, MySQL's is an alias for TINYINT(1) that accepts any integer, SQL Server has BIT, and Oracle had no SQL-level boolean at all until 23ai, which is why legacy Oracle schemas are full of CHAR(1) with a check constraint. And store UTC: timestamptz on PostgreSQL, datetime2 on SQL Server, TIMESTAMP WITH TIME ZONE on Oracle. MySQL's TIMESTAMP converts to UTC correctly but is bounded to 2038, which Magento uses extensively, and which is closer now than any migration plan.

Row width is the quietest performance factor in the whole system. Every engine reads pages, not rows. Halving row width doubles rows per page, which roughly halves scan I/O and doubles effective buffer cache capacity. So don't leave a rarely-read TEXT column sitting in a hot table — and sales_order_item.product_options is exactly that, a serialized blob in one of the busiest tables in the schema. PostgreSQL's TOAST handles this automatically; Oracle moves LOBs out of line above a threshold; SQL Server and MySQL are less automatic. This is also the real reason SELECT * in application code is a design smell: it defeats covering indexes, drags wide columns across the wire, and breaks silently the moment someone adds a column.

Constraints are performance features, not just safety features. NOT NULL lets the optimizer skip null-handling logic. UNIQUE tells it a join won't fan out, which changes cardinality estimates. And join elimination — where a validated foreign key lets SQL Server, Oracle, and PostgreSQL remove a LEFT JOIN entirely when you select nothing from the parent — is why views over well-constrained schemas outperform views over "we enforce it in the app" schemas. The corollary matters operationally: disabling constraints for a bulk load and re-enabling them NOVALIDATE on Oracle or untrusted on SQL Server leaves you with constraints the optimizer will not use. After any bulk import — and Magento imports are constant — check sys.foreign_keys.is_not_trusted on SQL Server and STATUS/VALIDATED in Oracle's USER_CONSTRAINTS.

Concurrency changes how you should write queries, and this is the largest operational difference between SQL Server and the other three:

Engine	Default isolation	Readers block writers?
PostgreSQL	Read Committed (MVCC)	No
Oracle	Read Committed (MVCC, undo-based)	No
MySQL/InnoDB	Repeatable Read (MVCC)	No
SQL Server	Read Committed with locking, unless RCSI is on	Yes

By default, a long report on SQL Server takes shared locks and blocks writers. This is why WITH (NOLOCK) is scattered through every legacy SQL Server codebase — and it's a bad fix, permitting dirty reads, missed rows, and duplicated rows from page splits mid-scan. The correct fix is enabling Read Committed Snapshot Isolation at the database level, which makes SQL Server behave like the others; understand the tempdb implications before flipping it on a live system. We turned RCSI on before the first report ran, and it was the single most important configuration change of the project. Meanwhile MySQL's Repeatable Read default is why you see gap-lock deadlocks on sequence_order_1 and inventory reservations during a flash sale — patterns that would be fine on PostgreSQL.

Schema change at scale. Magento gives you declarative schema (db_schema.xml, setup:upgrade), which handles versioning nicely and tells you nothing about how long the DDL takes on sixty million rows. Never rely on "it was fast in staging" — staging has ten thousand rows. Always set a lock timeout (lock_wait_timeout in MySQL, SET LOCK_TIMEOUT in SQL Server, SET lock_timeout in PostgreSQL), because a migration that queues behind a long query and then blocks every subsequent query is how a two-second ALTER takes down a site — and on PostgreSQL specifically, ALTER TABLE takes an ACCESS EXCLUSIVE lock, so even an "instant" change can cause a total outage if one long SELECT is in flight. Use expand/contract for anything breaking: add new, dual-write, backfill, switch reads, stop writing old, drop old. Four deploys, zero downtime. And on MySQL, know that gh-ost and pt-online-schema-change exist for the cases native online DDL doesn't cover, because on a large Magento install you will need them.

Now the honest part, and it's the paragraph I'd want a junior to remember longest. The second engine is not free. We took on a second schema to keep in sync, a nightly ETL that could fail silently, a second set of credentials and backups and patching, a second dialect for every developer to know, and a permanent question of "which one is right?" whenever two numbers disagreed. We wrote a reconciliation job — row counts and revenue sums per store per day, MySQL versus SQL Server, alerting on any variance over 0.01% — before we shipped a single report, and it caught three real ETL bugs in the first month. Do not stand up a second engine without standing up the thing that tells you it's lying to you.

---

What to take with you

Black Friday came. The reports held, because they'd stopped being queries on the production database and become tables on a machine that could actually do the work. Nothing about Magento's schema got smarter. We just stopped asking one engine to do things it had never been built to do.

Five things, if you retain nothing else:

State the grain of your result in one sentence before you write anything. Most bad SQL is a grain error wearing a DISTINCT.

NOT EXISTS, never NOT IN against a subquery. It's the only spelling that's correct on every engine, and it's also the fastest on the ones where it matters. And that ON DELETE SET NULL on sales_order_item.product_id is waiting for you right now.

Aggregate before you join. Joining first fans out rows and then you're summing an inflated set, and the wrong number will look plausible.

Every window ORDER BY gets a deterministic tiebreaker, and every ordered window aggregate gets an explicit ROWS frame. The first prevents reports that change without the data changing; the second prevents running totals that are secretly daily totals.

Denormalization is a maintenance obligation, not a shortcut. Magento's entire indexer subsystem is what that obligation looks like when the engine won't help you. Budget for it, and build the drift detector before you build the denormalization.

Before you commit a query

• [ ] Stated the result grain in one sentence
• [ ] No DISTINCT papering over a fan-out
• [ ] NOT EXISTS, not NOT IN, against any subquery
• [ ] No correlated aggregate subqueries in the SELECT list
• [ ] Aggregation happens before joining
• [ ] Outer-join predicates on the null-supplying side live in ON, not WHERE
• [ ] Every window ORDER BY has a deterministic tiebreaker
• [ ] Explicit ROWS BETWEEN on any ordered window aggregate
• [ ] UNION ALL unless dedupe is genuinely required
• [ ] No functions wrapped around indexed columns
• [ ] Parameter types match column types
• [ ] Read the plan against production-scale data, not staging

Before you commit a schema change

• [ ] Types as narrow as the domain allows; money is DECIMAL; timestamps timezone-explicit
• [ ] Every FK column has an index (free on InnoDB, which is why you'll forget it elsewhere)
• [ ] PK is narrow and monotonic
• [ ] Subtype exclusivity enforced by constraint, not by hope — and verify constraints are actually enforced on your version
• [ ] Any denormalized value has a documented rebuild path and a drift-detection job
• [ ] Large text and blob columns are out of the hot table
• [ ] Migration is expand/contract, with a lock timeout and a rollback plan
• [ ] You know how long it takes at production row counts

Reading the plan

Engine	Command
PostgreSQL	EXPLAIN (ANALYZE, BUFFERS, VERBOSE) — BUFFERS is the one people skip and shouldn't
MySQL	EXPLAIN ANALYZE (8.0.18+), EXPLAIN FORMAT=JSON
SQL Server	Actual execution plan; SET STATISTICS IO, TIME ON; Query Store for history
Oracle	DBMS_XPLAN.DISPLAY_CURSOR(format=>'ALLSTATS LAST') — the actual plan, not EXPLAIN PLAN's guess

Universally: look for estimated versus actual row counts diverging by more than about ten times, scans where you expected seeks, and any operator whose output explodes relative to its input.

---

On verifying any of this

Every version boundary above is checkable and you should check it, because they move and I have been wrong before.

For the Magento schema, don't trust any blog post including this one — read db_schema.xml in the relevant module in your own installed version and run SHOW CREATE TABLE. Adobe Commerce and Magento Open Source diverge (content staging changes catalog primary keys to row_id), and 2.4.x has changed definitions across minor releases. For MySQL, the Reference Manual's optimization chapter, and check MariaDB's knowledge base separately rather than assuming parity — it isn't the same database. For SQL Server, Microsoft Learn, and read the "Applies to" banner on every page, because the difference between GA in Azure SQL and preview on-prem is frequently the whole story. For Oracle, the SQL Language Reference for your exact release plus the New Features guide, since 19c, 21c, 23ai, and 26ai differ meaningfully and the version numbering does not help.

Where something here is version-gated, I've said so. Verify against the exact version in your environment before you rely on it. That isn't a disclaimer — it's the job.

---

Disclaimer: I used Claude to generate multiple detailed guides and then in different sessions fact check itself and correct for hallucinations. It's only as powerful as the model was trained to be, and it's being asked to act as an engineer I used to once be, and look at the questions that I never knew to ask and how the original list of requirements is a reference that is very technical and hard to read - but it contains a depth of knowledge that is a reference for a seasoned junior engineer looking to take on a Senior level role. Part of being a Senior is your ability to mentor junior engineers, and writing is the manner in which that I knowledge transfer to the next generation.