Optimizing a Slow MySQL Query: Converting EXISTS to JOIN and Replacing OR with UNION

Background

A developer received a SQL statement that was running very slowly. The execution plan showed a full table scan on the invoice_sales_application table, scanning 1.16 million rows, resulting in a 43‑second runtime.

Analysis of the Original SQL

explain SELECT count(0)
FROM invoice_sales_application a
WHERE (
    shop_order_id LIKE '23060919546335%'
    OR (
        EXISTS (
            SELECT 1
            FROM invoice_sales_application_detail b
            WHERE a.application_no = b.application_no
              AND a.invoice_category = b.invoice_category
              AND b.del_flag = 0
              AND b.shop_order_id LIKE '23060919546335%'
        )
        AND a.is_merge = 1
    )
)

The query filters rows by a shop_order_id prefix and optionally by matching records in the invoice_sales_application_detail table. Because of the EXISTS and the OR , the optimizer cannot use indexes efficiently, leading to the full scan.

Optimization Steps

1. Convert EXISTS to INNER JOIN

Rewrite the sub‑query as an INNER JOIN so that the join can leverage indexed columns.

SELECT count(0)
FROM invoice_sales_application a
INNER JOIN invoice_sales_application_detail b
  ON a.application_no = b.application_no
WHERE (
    a.shop_order_id LIKE '23060919546335%'
    OR (
        b.shop_order_id LIKE '23060919546335%'
        AND a.is_merge = 1
    )
)
  AND a.invoice_category = b.invoice_category
  AND b.del_flag = 0;

Although the join is introduced, the presence of OR still prevents index usage, and the plan remains all+ref with a full scan.

2. Replace OR with UNION

Separate the two logical branches into independent queries and combine the results with UNION , allowing each branch to use its own index.

SELECT count(*)
FROM invoice_sales_application a
INNER JOIN invoice_sales_application_detail b
  ON a.application_no = b.application_no
WHERE a.shop_order_id = '23060919546335'
  AND a.del_flag = 0
  AND a.invoice_category = b.invoice_category
  AND b.del_flag = 0
UNION
SELECT count(*)
FROM invoice_sales_application a
INNER JOIN invoice_sales_application_detail b
  ON a.application_no = b.application_no
WHERE b.shop_order_id = '23060919546335'
  AND a.is_merge = 1
  AND a.del_flag = 0
  AND a.invoice_category = b.invoice_category
  AND b.del_flag = 0;

The new execution plan shows eq_ref+ref+ref+ref , indicating that indexes are now being used and the full table scan is eliminated.

Result

After applying both transformations, the query time dropped from 42 seconds to 18 ms, a several‑order‑of‑magnitude improvement.

Conclusion

When a SQL statement contains EXISTS , consider rewriting it as an INNER JOIN and test the performance impact. If the WHERE clause includes an OR , converting it to a UNION of separate queries often enables index usage and dramatically speeds up execution.