MySQL左连接OR条件导致查询过慢的优化方案咨询

Hey there! Let's figure out how to speed up that laggy query. The core problem here is using OR in your LEFT JOIN clause—databases struggle to leverage indexes effectively with OR conditions, which often triggers slow full-table scans. Here are practical, optimized fixes you can implement right away:

1. Split the JOIN into Two Queries with UNION ALL #

Instead of forcing a single JOIN with an OR, split it into two separate LEFT JOINs (one for each email type) and combine the results. This lets the database use indexes on customers.customer_email for each join, which is far more efficient.

SELECT o.order_id, o.total, c.customer_email
FROM orders o
LEFT JOIN customers c 
  ON o.bill_email = c.customer_email
WHERE o.order_date >= '2020-06-01' 
  AND o.order_date < '2020-07-01' -- Avoid DATE() function to use index on order_date

UNION ALL

SELECT o.order_id, o.total, c.customer_email
FROM orders o
LEFT JOIN customers c 
  ON o.ship_email = c.customer_email
WHERE o.order_date >= '2020-06-01' 
  AND o.order_date < '2020-07-01'
  AND o.bill_email != o.ship_email -- Skip duplicates when emails match

Why this works: #

• Each JOIN uses a single equality condition, which is index-friendly.
• Replacing DATE(o.order_date) >= '2020-06-01' with a range check lets the database use an index on orders.order_date (function calls like DATE() block index usage).
• The AND o.bill_email != o.ship_email in the second query prevents duplicate rows for orders where billing and shipping emails are identical.

2. Use LATERAL JOIN (MySQL 8.0+, PostgreSQL, SQL Server) #

If your database supports it, a LATERAL JOIN (or CROSS APPLY in SQL Server) lets you "unpivot" the two email fields into separate rows first, then join to customers once with a single condition.

SELECT o.order_id, o.total, c.customer_email
FROM orders o
-- Unpivot bill and ship emails into individual rows
JOIN LATERAL (
    SELECT email
    FROM (
        SELECT o.bill_email AS email
        UNION ALL
        SELECT o.ship_email AS email
    ) AS email_list
    -- DISTINCT ensures no duplicate rows if emails are the same
    GROUP BY email
) AS e
LEFT JOIN customers c 
  ON e.email = c.customer_email
WHERE o.order_date >= '2020-06-01' 
  AND o.order_date < '2020-07-01'

Why this works: #

• The LATERAL subquery creates a row for each unique email per order, turning the OR condition into a simple equality join. This makes it trivial for the database to use the customer_email index.

3. Verify Critical Indexes Are in Place #

Before making query changes, double-check these indexes exist—they're foundational for all the above optimizations:

• A non-clustered index on customers.customer_email (a unique index is even better if emails are unique per customer).
• A non-clustered index on orders.order_date to speed up the date filter.

Final Tip #

If you still see slow performance after these tweaks, run an EXPLAIN on your query. This will show you exactly where bottlenecks are—like whether indexes are being used, or if there are missing statistics slowing things down.

内容的提问来源于stack exchange，提问作者Jenz