A scheduling query can look straightforward until it has to find an order through two different relationships. One relationship links the order to an appointment. The other links it to the appointment’s benefit and goods type.
In the query that prompted this investigation, both relationships appeared inside a LEFT JOIN condition. The surrounding query also included one-to-many relationships and aggregation, making it harder to separate expensive data access from unexpected row counts.
The useful lesson is not that OR should always be removed. It is that “match either relationship” and “prefer one relationship, otherwise fall back” are different requirements. Before tuning the execution plan, the query must express the intended requirement.
This article uses simplified SQL based on that case. The examples clarify matching behavior and propose changes to evaluate. They do not report a measured production speedup: the original execution plans, full schema, and comparable timing results are not available.
The original join: two matching paths, no priority
The original condition had this shape:
LEFT JOIN archiver_order AS o
ON (
o.is_consume = a.id
OR (
o.benefit_id = a.benefit_id
AND o.goods_type = a.goods_type
)
)
AND o.enabled_flag = 1Despite its name, is_consume acts as an appointment reference in this example; it is not treated as a Boolean.
This condition accepts an enabled order if either path matches. It does not tell MySQL to prefer the consumption relationship.
There is another important detail: goods_type applies only to the benefit branch in this original excerpt. Moving it outside the OR changes which consumption orders qualify.
The two paths may benefit from different indexes. An index beginning with is_consume supports a different lookup from one beginning with benefit_id and goods_type. Whether the combined condition becomes expensive depends on the data, available indexes, other filters, and chosen execution plan. OR alone is not evidence of a full scan or a slow query.
Decide what a correct result looks like
For one appointment, imagine these eligible order IDs:
- Consumption matches: 101, 102, 103.
- Benefit matches: 103, 104, 105, 106.
An OR join returns six orders: 101 through 106. Order 103 appears once because it is one right-hand row satisfying both predicates.
A consumption-first fallback returns three orders: 101, 102, and 103. Benefit-only matches are excluded because eligible consumption matches exist.
The difference is a business rule, not merely an alternative execution strategy. Both LEFT JOIN approaches preserve the appointment with NULL order columns when neither path matches.
More generally, the number of matching orders for a single OR join is:
Consumption matches + benefit matches − overlapping matches
Three consumption matches and four benefit matches therefore yield at most seven matching order rows at this join. Additional independent joins may multiply those rows later.
Before choosing a rewrite, answer this question:
Should the result include every order that matches either relationship, or should benefit orders be used only when no eligible consumption order exists?
The rest of the article keeps these two contracts separate.
Use consistent eligibility rules when comparing queries
The later version of the business query introduced additional order filters. To avoid confusing eligibility changes with join changes, all comparison examples below use the same definition:
WITH eligible_order AS (
SELECT id, is_consume, benefit_id, goods_type,
status, payment_status
FROM archiver_order
WHERE enabled_flag = 1
AND is_consume_type = 1
AND status <> 3
)Every matching branch also requires the order’s goods_type to equal the appointment’s goods_type.
These are normalized examples, not exact equivalents of the original excerpt: they add the consume-type and status filters and apply the goods-type requirement to both paths. Confirm these rules with the business before adopting them.
The examples assume that order ID is a non-null primary key. Ordinary equality does not match NULL benefit IDs. Also, status <> 3 excludes NULL statuses; include them explicitly only if the business definition requires that.
The CTE makes the common filters readable. It does not guarantee a faster plan. MySQL can merge or materialize a CTE; if it materializes a CTE, that materialization is performed once for the query even when referenced multiple times. Inspect the plan instead of assuming either repeated evaluation or automatic optimization. MySQL derived-table and CTE optimization
If both paths matter, preserve OR semantics
Using the shared eligible_order CTE above, the all-matches query body is:
SELECT a.id AS appointment_id,
o.id AS order_id,
o.payment_status,
o.status
FROM appointment AS a
LEFT JOIN eligible_order AS o
ON o.goods_type = a.goods_type
AND (
o.is_consume = a.id
OR o.benefit_id = a.benefit_id
)
ORDER BY a.id, o.id;One possible alternative is to split the ID lookups into a correlated UNION:
SELECT a.id AS appointment_id,
o.id AS order_id,
o.payment_status,
o.status
FROM appointment AS a
LEFT JOIN eligible_order AS o
ON o.id IN (
SELECT c.id
FROM eligible_order AS c
WHERE c.is_consume = a.id
AND c.goods_type = a.goods_type
UNION
SELECT b.id
FROM eligible_order AS b
WHERE b.benefit_id = a.benefit_id
AND b.goods_type = a.goods_type
)
ORDER BY a.id, o.id;Prepend the same eligible_order CTE to either query body. Complete runnable versions are included with the examples.
With these shared filters and a unique order ID, the two queries express the same matching requirement. Importantly, the enabled and status filters have not disappeared during the rewrite.
However, separating the predicates does not prove that the second query is faster. The subquery references the outer appointment, and UNION introduces duplicate elimination. The actual evaluation strategy and loop counts must come from the execution plan.
Do not describe it as “exactly one UNION execution per appointment” without plan evidence. MySQL’s documented semijoin transformation requirements include a single SELECT without UNION, so a UNION-based subquery should not be assumed to receive that transformation. MySQL semijoin and antijoin transformations
If the original OR plan is efficient and the results are correct, keeping it may be the best choice.
If the requirement is fallback, make that rule explicit
For consumption-first fallback, use two guarded joins:
WITH eligible_order AS (
SELECT id, is_consume, benefit_id, goods_type,
status, payment_status
FROM archiver_order
WHERE enabled_flag = 1
AND is_consume_type = 1
AND status <> 3
)
SELECT a.id AS appointment_id,
CASE WHEN c.id IS NOT NULL THEN c.id
ELSE b.id END AS order_id,
CASE WHEN c.id IS NOT NULL THEN c.payment_status
ELSE b.payment_status END AS payment_status,
CASE WHEN c.id IS NOT NULL THEN c.status
ELSE b.status END AS status
FROM appointment AS a
LEFT JOIN eligible_order AS c
ON c.is_consume = a.id
AND c.goods_type = a.goods_type
LEFT JOIN eligible_order AS b
ON c.id IS NULL
AND b.benefit_id = a.benefit_id
AND b.goods_type = a.goods_type
ORDER BY a.id, order_id;The condition c.id IS NULL is the key. It allows the benefit branch to match only when the consumption join has produced no eligible order.
“Eligible” matters. A disabled consumption order, an excluded status, or a different goods type does not block fallback under these rules.
The CASE expressions select fields from the chosen row source. If a consumption order exists but its payment_status is NULL, that NULL is preserved; the query does not switch to a benefit order merely to fill the field.
COALESCE(c.payment_status, b.payment_status) is also safe for this specific guarded arrangement because the benefit branch cannot match when c.id is non-null. CASE makes the row-selection rule more explicit and is easier to review if the query later changes.
Keep right-side eligibility filters in the join logic or the shared CTE. Moving a condition such as c.status <> 3 into an outer WHERE clause can discard appointments with no matched order and undermine the intended LEFT JOIN behavior.
Fallback still does not mean one order per appointment
If three eligible consumption orders exist, the query returns three rows. If none exist and four eligible benefit orders exist, it returns four rows.
When the application requires exactly one order, define a separate selection rule. That could be enforced uniqueness or a deterministic ordering based on business meaning. Do not silently substitute MAX(id), MAX(status), or “the latest order” unless that is the agreed requirement.
If the application conditionally exposes an order ID only for a particular payment and status combination, apply that condition to the selected order’s fields. It should not quietly become another fallback rule.
Investigate row multiplication beyond the order join
Even a well-indexed order lookup may feed too many rows into the rest of the query.
Suppose an appointment has:
- Five label rows.
- Three order rows.
- Eight log rows.
When all three tables are joined independently by appointment ID, that appointment can produce:
5 × 3 × 8 = 120 intermediate rows
Pre-aggregation changes the grain of each input. Use it when the output needs summaries rather than every combination of detail rows.
A final GROUP BY does not prevent those intermediate rows from being formed. It can also conceal incorrect aggregates: each order amount might be repeated once for every matching label and log combination.
GROUP BY is not a general-purpose deduplication repair. Selecting nonaggregated fields that are not functionally dependent on the grouping keys may be rejected with ONLY_FULL_GROUP_BY enabled; when permitted without that mode, the selected values may be nondeterministic. MySQL GROUP BY handling
Choose the shape of each relationship according to what the output needs:
| Required output | Suitable approach |
|---|---|
| Whether a qualifying order exists | EXISTS |
| Number of qualifying orders | Aggregate by appointment before joining |
| One specific order | Define deterministic row selection |
| Every order detail | Keep the detail rows and account for their multiplicity |
| A summary of each independent child table | Aggregate each child to the required grain |
For example, if the only question is whether a valid consumption order exists:
SELECT a.id,
EXISTS (
SELECT 1
FROM archiver_order AS o
WHERE o.is_consume = a.id
AND o.goods_type = a.goods_type
AND o.enabled_flag = 1
AND o.is_consume_type = 1
AND o.status <> 3
) AS has_consumption_order
FROM appointment AS a;This expresses an existence requirement directly; it is not a universal performance guarantee.
For pre-aggregation, avoid scanning and summarizing an entire historical child table when the request only concerns a small appointment set. Consider restricting aggregation to relevant appointment IDs and verify the resulting plan.
Also avoid treating MAX(payment_status) as “the status of the chosen order.” It is the greatest status value in a group, which may have no relationship to the desired order.
Evaluate indexes for the actual lookup paths
The two normalized matching paths suggest these candidate indexes:
CREATE INDEX idx_order_consume_lookup
ON archiver_order (
is_consume,
goods_type,
enabled_flag,
is_consume_type,
status
);
CREATE INDEX idx_order_benefit_lookup
ON archiver_order (
benefit_id,
goods_type,
enabled_flag,
is_consume_type,
status
);These are starting points for testing, not a prescription to add both indexes unchanged.
The leading columns reflect the two relationship lookups. Subsequent columns reflect eligibility predicates. The status inequality may influence how much of the index can be used for range access versus filtering.
Column order should follow actual access patterns, usable leftmost prefixes, equality and range conditions, and reuse across other queries. “Put the most selective column first” is not a sufficient design rule. Review existing indexes before adding overlapping ones. MySQL multiple-column indexes
Additional indexes increase storage and write-maintenance costs. Whether including another selected field is worthwhile depends on the workload and measured benefit.
Check join-column compatibility as well: matching identifiers should have compatible types, and string joins should use appropriate character sets and collations. Avoid wrapping indexed join keys in casts or functions without understanding the access consequences.
Compare execution plans and measured work
Use the full query with representative store, date, permission, and status filters. A plan for an isolated join may not explain the behavior of the production query.
A practical comparison has two stages:
- Inspect the estimated plan.
- Execute controlled measurements and inspect actual work.
For the estimated plan:
EXPLAIN FORMAT = TREE
SELECT ...;For actual execution details, MySQL 8.0.18 and later provide:
EXPLAIN ANALYZE
SELECT ...;These commands are templates: substitute the complete query. EXPLAIN ANALYZE executes the statement, so use a suitable test environment or a controlled production window for an expensive query. MySQL EXPLAIN documentation
Compare:
- Estimated versus actual rows at important joins.
- Actual loop counts for repeated lookups or subqueries.
- Rows reaching aggregation and sorting.
- Access paths and the indexes actually selected.
- End-to-end elapsed time and returned row count.
Where nodes run repeatedly, interpret reported per-loop rows and timing together with loop counts. Do not add all node times as if they were independent; parent timings can include child work.
Keep the output format clear when documenting plans. Traditional EXPLAIN includes fields such as key_len, rows, and Extra. JSON uses fields such as key_length, rows_examined_per_scan, and attached_condition. Avoid presenting a mixed list as though those names belong to one format.
A filesort indication means an extra sorting operation; it does not by itself prove that the sort spilled to disk.
Record the MySQL version, data volume and distribution, relevant indexes, parameter set, result count, and repeated runtimes. Separate first-run observations from warm-cache observations without assuming that the first run was genuinely cold.
Change one factor at a time where possible. If join semantics, indexes, and aggregation all change together, a faster query will not reveal which change helped—or whether fewer results caused the apparent improvement.
Validate results separately from performance
For an equivalence-preserving rewrite, compare complete result multisets: row values and how often each row appears. Matching total counts or distinct IDs is not enough.
For an intentional change to fallback semantics, compare against the new business contract. Differences from the OR result may be correct, but they must be understood and documented.
The accompanying small fixture covers fourteen cases, including:
| Case | Expected fallback behavior |
|---|---|
| Neither relationship matches | Preserve the appointment with NULL order fields |
| Only benefit matches exist | Return eligible benefit matches |
| Both relationships match | Return eligible consumption matches only |
| The same order matches both relationships | Do not create an extra copy merely because both predicates are true |
| Consumption order is disabled or otherwise ineligible | Allow eligible benefit matches |
| Consumption payment status is NULL | Keep the consumption row and its NULL value |
| Multiple orders match the selected branch | Preserve the multiple matches |
| Both benefit keys are NULL | Ordinary equality does not match them |
The fixture also checks that the normalized OR and correlated UNION variants return the same multisets.
Validation scope: all fourteen cases passed using SQLite for a portable SQL semantic check. This verifies the illustrated matching behavior; it does not validate MySQL optimizer behavior, deployment compatibility, or performance. Run the supplied query variants against the target MySQL version and representative data before deployment.
For production-sized comparisons, use a consistent snapshot or a stable dataset so that concurrent changes do not create false differences. Compare NULL values explicitly and preserve duplicate counts.
Do not use a single GROUP_CONCAT of IDs as proof of equality. Its result is length-limited, it does not verify all fields, and an ID list alone can miss changes in row multiplicity. MySQL aggregate-function documentation
What this case changes about the tuning process
The investigation leads to two separate decisions.
First, choose the correct matching contract. Keep all matching orders if both relationships matter. Use guarded joins when the business explicitly requires eligible consumption orders first and benefit orders only as fallback.
Second, reduce the work needed to implement that contract. Evaluate indexes for each lookup path, control independent one-to-many relationships, and measure the actual rows and loops in the full execution plan.
A rewrite is successful when it returns the intended results and demonstrates an improvement under representative conditions. Removing OR, adding a CTE, or shortening the SQL is not enough on its own.