MySQL Query Optimization: Reducing Joins and Subqueries to Boost Performance

The author received a SQL query that took 9 seconds to return results and decided to optimize it as a DBA.

Original SQL

SELECT
    a.id,
    a.order_no,
    a.line_no,
    a.customer_code,
    a.customer_name,
    a.cost_item,
    a.cost_item_name,
    a.payee_type,
    a.payee_code,
    a.payee_name,
    a.tax_plan,
    a.expenses_amount,
    a.pre_tax_amount,
    a.tax_amount,
    a.start_date,
    a.end_date,
    a.accrued_amount,
    a.paid_amount,
    a.reimbursed_amount,
    a.is_close,
    a.payment_type,
    a.settlement_date,
    a.source_order_no,
    a.source_order_line,
    a.invoice_type,
    a.del_flag,
    a.create_time,
    a.create_by_id,
    a.create_by,
    a.update_time,
    a.update_by_id,
    a.update_by,
    a.remark,
    a.shop_code,
    a.close_reason,
    a.close_content,
    a.budget_period,
    a.shop_name,
    a.product_category_code,
    a.product_category_name,
    a.product_code,
    a.product_name,
    a.product_path_code,
    a.shop_classify_name,
    a.shop_level_require,
    a.strategy_code,
    a.product_path_name
 FROM
    expense_application_detail a
 LEFT JOIN expense_application b ON a.order_no = b.order_no
    AND b.del_flag = 0
 WHERE
    a.del_flag = 0
    AND b.data_source = 8
    AND b.order_type = 'B2BFYSQ01'
    AND b.order_status IN (3, 8)
    AND a.is_close = 0
    AND EXISTS (
        SELECT 1
        FROM expense_application_detail d1
        LEFT JOIN base_customer_info c1 ON d1.customer_code = c1. CODE
            AND c1.del_flag = 0
        LEFT JOIN budget_system_ecommerce_channel d ON d. CODE = c1.business_channel_code
            AND d.del_flag = 0
        WHERE d1.order_no = a.order_no
            AND d.del_flag = 0
            AND d.business_category_code = '01'
            AND (
                d.business_mode_code = '33'
                OR (
                    d.business_mode_code = 'C010106'
                    AND d. CODE = 'C01010602'
                )
            )
    );

The execution plan shows that the join types are already optimal, but further improvements are possible.

Optimization ideas

Remove unnecessary JOINs and subquery references. The subquery references the same table expense_application_detail as the outer query alias a , so the condition can be moved outside and the subquery eliminated, avoiding repeated scans of expense_application_detail .

Optimized SQL

SELECT
    a.id,
    a.order_no,
    a.line_no,
    a.customer_code,
    a.customer_name,
    a.cost_item,
    a.cost_item_name,
    a.payee_type,
    a.payee_code,
    a.payee_name,
    a.tax_plan,
    a.expenses_amount,
    a.pre_tax_amount,
    a.tax_amount,
    a.start_date,
    a.end_date,
    a.accrued_amount,
    a.paid_amount,
    a.reimbursed_amount,
    a.is_close,
    a.payment_type,
    a.settlement_date,
    a.source_order_no,
    a.source_order_line,
    a.invoice_type,
    a.del_flag,
    a.create_time,
    a.create_by_id,
    a.create_by,
    a.update_time,
    a.update_by_id,
    a.update_by,
    a.remark,
    a.shop_code,
    a.close_reason,
    a.close_content,
    a.budget_period,
    a.shop_name,
    a.product_category_code,
    a.product_category_name,
    a.product_code,
    a.product_name,
    a.product_path_code,
    a.shop_classify_name,
    a.shop_level_require,
    a.strategy_code,
    a.product_path_name
 FROM
    expense_application_detail a
 LEFT JOIN expense_application b ON a.order_no = b.order_no
    AND b.del_flag = 0
 WHERE
    a.del_flag = 0
    AND b.data_source = 8
    AND b.order_type = 'B2BFYSQ01'
    AND b.order_status IN (3, 8)
    AND a.is_close = 0
    AND EXISTS (
        SELECT 1
        FROM base_customer_info c1
        LEFT JOIN budget_system_ecommerce_channel d ON c1.business_channel_code = d.code
            AND d.del_flag = 0
        WHERE c1.code = a.customer_code
            AND c1.del_flag = 0
            AND d.business_category_code = '01'
            AND (
                d.business_mode_code = '33'
                OR (d.business_mode_code = 'C010106' AND d.code = 'C01010602')
            )
    );

After the changes the query runs in 0.2 seconds, a several‑order‑of‑magnitude improvement.

Conclusion

In the original query the table expense_application_detail was referenced multiple times—once in the main query and again inside an EXISTS subquery—causing the database engine to scan the same table repeatedly. By simplifying the query, removing redundant joins, and moving conditions outward, performance is dramatically increased.