Summary
Dolt median latency is 1.76x MySQL
MySQL 8.4.11 median: 3.854 ms
Dolt 2.3.1 median: 6.786 ms
Dolt/MySQL ratio: 1.76x
Reproduction
Standalone reproduction: repro.sql
The generated script is standalone and creates querylab_repro_25804b89a803b42a, loads 3500 deterministic rows, analyzes the retained tables, and runs the query. It preserves all rows and indexes for tables referenced by the query; unrelated tables were removed.
WITH item_totals AS (SELECT order_id, SUM(quantity * unit_price) computed_total FROM order_items GROUP BY order_id), mismatches AS (SELECT o.id, o.customer_id, o.total, i.computed_total FROM orders o JOIN item_totals i ON i.order_id=o.id WHERE ABS(o.total-i.computed_total)>100) SELECT c.region, COUNT(*) mismatch_count FROM mismatches m JOIN customers c ON c.id=m.customer_id GROUP BY c.region ORDER BY mismatch_count DESC, c.region
Plan difference summary
- Join strategy differs: MySQL 8.4.11 uses nested-loop join; Dolt 2.3.1 uses index lookup join.
- Join input order differs: MySQL 8.4.11 accesses temporary → i → o → c; Dolt 2.3.1 accesses orders → customers.
- Scan targets differ: MySQL 8.4.11 scans temporary, i; Dolt 2.3.1 scans order_items.
- Index choices differ: MySQL 8.4.11 uses idx_items_order, PRIMARY, PRIMARY; Dolt 2.3.1 uses orders.id, customers.id.
- Aggregation strategy differs: MySQL 8.4.11 materializes an intermediate result; Dolt 2.3.1 does not show materialization.
MySQL 8.4.11 explain plan
-> Sort: mismatch_count DESC, c.region
-> Table scan on <temporary>
-> Aggregate using temporary table
-> Nested loop inner join (cost=1674 rows=860)
-> Nested loop inner join (cost=1088 rows=860)
-> Table scan on i (cost=488..502 rows=860)
-> Materialize CTE item_totals (cost=488..488 rows=860)
-> Group aggregate: sum((order_items.quantity * order_items.unit_price)) (cost=402 rows=860)
-> Index scan on order_items using idx_items_order (cost=202 rows=2000)
-> Filter: (abs((o.total - i.computed_total)) > 100) (cost=0.25 rows=1)
-> Single-row index lookup on o using PRIMARY (id=i.order_id) (cost=0.25 rows=1)
-> Single-row index lookup on c using PRIMARY (id=o.customer_id) (cost=0.25 rows=1)
Dolt 2.3.1 explain plan
Project
├─ columns: [c.region, count(1) as mismatch_count]
└─ Sort(mismatch_count DESC, c.region ASC)
└─ Project
├─ columns: [count(1), c.region, count(1) as mismatch_count]
└─ GroupBy
├─ select: COUNT(1), c.region
├─ group: c.region
└─ LookupJoin
├─ SubqueryAlias
│ ├─ name: m
│ ├─ outerVisibility: false
│ ├─ isLateral: false
│ ├─ cacheable: true
│ ├─ colSet: (21-24)
│ ├─ tableId: 8
│ └─ Project
│ ├─ columns: [o.id, o.customer_id, o.total, i.computed_total]
│ └─ Filter
│ ├─ (abs((o.total - i.computed_total)) > 100)
│ └─ LookupJoin
│ ├─ SubqueryAlias
│ │ ├─ name: i
│ │ ├─ outerVisibility: false
│ │ ├─ isLateral: false
│ │ ├─ cacheable: true
│ │ ├─ colSet: (15,16)
│ │ ├─ tableId: 5
│ │ └─ Project
│ │ ├─ columns: [order_items.order_id, sum((order_items.quantity * order_items.unit_price)) as computed_total]
│ │ └─ GroupBy
│ │ ├─ select: SUM((order_items.quantity * order_items.unit_price)), order_items.order_id
│ │ ├─ group: order_items.order_id
│ │ └─ Table
│ │ ├─ name: order_items
│ │ └─ columns: [order_id quantity unit_price]
│ └─ TableAlias(o)
│ └─ IndexedTableAccess(orders)
│ ├─ index: [orders.id]
│ ├─ columns: [id customer_id total]
│ └─ keys: i.order_id
└─ TableAlias(c)
└─ IndexedTableAccess(customers)
├─ index: [customers.id]
├─ columns: [id region]
└─ keys: m.customer_id
QueryLab finding: 25804b89a803b42a
Summary
Dolt median latency is 1.76x MySQL
MySQL 8.4.11 median: 3.854 ms
Dolt 2.3.1 median: 6.786 ms
Dolt/MySQL ratio: 1.76x
Reproduction
Standalone reproduction: repro.sql
The generated script is standalone and creates
querylab_repro_25804b89a803b42a, loads 3500 deterministic rows, analyzes the retained tables, and runs the query. It preserves all rows and indexes for tables referenced by the query; unrelated tables were removed.Plan difference summary
MySQL 8.4.11 explain plan
Dolt 2.3.1 explain plan
QueryLab finding:
25804b89a803b42a