SARGable Condition Rewriting: Practical Solutions for Date Filtering Optimization

I. Introduction

Revealing the Astonishing Potential of SQL Optimization: **A simple condition rewrite slashed query costs by 99.79% and reduced execution time by 77.12%!**

In this in-depth experiment based on a real production environment, we witnessed the revolutionary effect of SARGable condition optimization technology in complex multi-table join queries. By eliminating function calls on date columns, the optimizer successfully transformed the heavy burden of a full table scan of 9,861 rows into an intelligent short-circuit query with “zero scans”. This remarkable optimization not only validates the effectiveness of the technical solution but also provides a reusable optimization paradigm for developers facing similar performance bottlenecks.

II. Test Environment Setup

1. Table Structure Design

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
-- Enterprise Information Master Table (10,000 records)
CREATE TABLE client_Corp (
    CorpGUID VARCHAR(200) PRIMARY KEY,
    CorpType VARCHAR(50),
    OwnType VARCHAR(50),
    ZoneName VARCHAR(100),
    Address VARCHAR(500),
    LinkMan VARCHAR(100),
    LinkMobile VARCHAR(50),
    LinkPhone VARCHAR(50),
    CorpFax VARCHAR(50),
    LinkEmail VARCHAR(100),
    USCC VARCHAR(100),
    OrangeCode VARCHAR(100),
    MANAGECODE VARCHAR(100),
    ZoneCode VARCHAR(100),
    CorpTypeCode VARCHAR(50)
) ENGINE=InnoDB;

-- Business Application Table (10,000 records)
CREATE TABLE client_ApplyA_TZ (
    ApplyGUID VARCHAR(200) PRIMARY KEY,
    ManaRange TEXT,
    Register DECIMAL(18,2)
) ENGINE=InnoDB;

-- Business Master View (10,000 records)  
CREATE TABLE v_Workmain_New (
    ApplyGUID VARCHAR(200) PRIMARY KEY,
    MainGUID VARCHAR(200),
    CorpGUID VARCHAR(200),
    QyjwCode VARCHAR(200),
    CountDate DATETIME,
    ItemCode VARCHAR(100),
    ItemName VARCHAR(200),
    YwlxCode VARCHAR(50),
    YwlxName VARCHAR(100),
    GbCode VARCHAR(200),
    GbName VARCHAR(200),
    Province VARCHAR(100),
    CityName VARCHAR(100),
    HyCode VARCHAR(50),
    HyName VARCHAR(200),
    JnName VARCHAR(200),
    IssueDate DATE,
    ApproveNumber VARCHAR(100),
    ApproveOrder INT,
    OpenMode VARCHAR(10),
    Capitalzj DECIMAL(18,2),
    CapitalCzj DECIMAL(18,2),
    INDEX idx_CorpGUID (CorpGUID),
    INDEX idx_QyjwCode (QyjwCode),
    INDEX idx_CountDate (CountDate),
    INDEX idx_GbCode (GbCode)
) ENGINE=InnoDB;

-- Enterprise Coordinates Table (10,000 records)
CREATE TABLE admin_WorkQyjw (
    QyjwCode VARCHAR(200) PRIMARY KEY,
    CurrStatus INT,
    CurrStatusName VARCHAR(100),
    JwName VARCHAR(200)
) ENGINE=InnoDB;

-- Country Code Table (10,000 records)
CREATE TABLE code_bmgb (
    gb_code VARCHAR(200) PRIMARY KEY,
    lm_code VARCHAR(100),
    lm_name VARCHAR(200),
    isApprove TINYINT,
    isNewMark TINYINT,
    isSilk TINYINT,
    isOne TINYINT,
    isPor TINYINT,
    isSpain TINYINT,
    isSouthP TINYINT,
    isRCEP TINYINT
) ENGINE=InnoDB;

-- Industry Code Table (10,000 records)
CREATE TABLE code_bmhy (
    hy_code VARCHAR(200) PRIMARY KEY,
    hy_name VARCHAR(200)
) ENGINE=InnoDB;

2. Test Data Generation

This test uses mirror data from the production environment, with each table containing 10,000 records, resulting in a total dataset of 60,000 records. The data distribution simulates real business scenarios, incorporating various date ranges, industry types, and enterprise status combinations.

III. SQL Optimization

1. Original SQL

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
select RowNumber,YwlxName,ItemName,IssueDate,JnName,JwName,lm_name,GbName,Province,CityName,Capital,CapitalC,Register,
(case when BigHyCode='A' then 'Primary Industry' when LOCATE(BigHyCode, ';B;C;D;E;') > 0 then 'Secondary Industry' else 'Tertiary Industry' end) BigHyCode_Name,
h.hy_name BigHyName,HyName,OpenMode,ManaRange,CurrStatusName,OwnType,CorpType,ZoneName,JnAddress,LinkMan,LinkMobile,LinkPhone,
CorpFax,LinkEmail,(case when item='1' then 'YES' else 'NO' end) item_Name,USCC,OrangeCode,CountDate,ApproveOrder,ApproveNumber,QyjwCode
,MainGUID,ItemCode,YwlxCode,GbCode,HyCode,Currstatus,BigHyCode,item
from (
    select row_number() over (order by a.YwlxCode,b.CountDate desc) as RowNumber,
    a.MainGUID, DATE_FORMAT(b.CountDate, '%Y-%m-%d') as CountDate, a.QyjwCode,q.JwName,a.OpenMode,a.ItemCode,a.ItemName,a.YwlxCode,a.YwlxName,a.GbCode,a.GbName,b.lm_name,a.Province,a.CityName,
    left(ltrim(a.HyCode),1) BigHyCode,a.HyCode,a.HyName,t.ManaRange,b.Capital,b.CapitalC,t.Register,a.JnName,c.CorpType,c.OwnType,c.ZoneName,c.Address JnAddress,c.LinkMan,c.LinkMobile,c.LinkPhone,c.CorpFax,c.LinkEmail,q.Currstatus,q.CurrstatusName,
    c.USCC,c.OrangeCode,a.IssueDate,a.ApproveNumber,a.ApproveOrder,b.item
    from (
        select a.ApplyGUID,a.CorpGUID,a.MainGUID,b1.QyjwCode,
        DATE_FORMAT(b1.CountDate, '%Y-%m-%d') as CountDate, b1.Capital,b1.CapitalC,b1.item,gb.lm_name
        from (
            select a.QyjwCode,max(a.CountDate) CountDate,sum(IFNULL(Capitalzj,0)) Capital,sum(IFNULL(CapitalCzj,0)) CapitalC,sum((case when a.ItemCode like '%A%' then 1 else 0 end)) as item
            from v_Workmain_New a
            inner JOIN client_ApplyA_TZ t on a.ApplyGUID = t.ApplyGUID
            inner JOIN client_Corp c ON a.CorpGUID=c.CorpGUID
            inner JOIN admin_WorkQyjw q ON a.QyjwCode = q.QyjwCode
            where DATE(a.countdate) >= @startrq and DATE(a.countdate) <= @endrq
            and IFNULL(c.MANAGECODE,'') like CONCAT('%', RTRIM(@slzone), '%')
            and IFNULL(c.ZoneCode,'') like CONCAT('%', RTRIM(@zg), '%')
            and IFNULL(c.Address,'') like CONCAT('%', RTRIM(@txtAddr), '%')
            and IFNULL(c.CorpTypeCode,'') like CONCAT('%', RTRIM(@corpType), '%')
            and a.OpenMode=(case when @openMode='9' then a.OpenMode else @openMode end)
            and q.CurrStatus=(case when @CurrStatus=9 then q.CurrStatus else @CurrStatus end)
            and 1=(case when @ownTypes='' then 1 else (case when LOCATE(c.OwnType, @ownTypes) > 0 then 1 else 0 end) end)
            group by a.QyjwCode
        ) b1,v_Workmain_New a,code_bmgb gb
        where a.QyjwCode=b1.QyjwCode and a.CountDate=b1.CountDate and a.GbCode=gb.gb_code
        and IFNULL(a.HyCode,'') like CONCAT('%', RTRIM(@hy), '%')
        and gb.lm_code like CONCAT('%', RTRIM(@lm), '%')
        and a.GbCode like CONCAT('%', RTRIM(@gb), '%')
        and ( gb.isApprove=(case when LOCATE('isApprove', @gbTypeCodes) = 0 then gb.isApprove else 1 end)
        and gb.isNewMark=(case when LOCATE('isNewMark', @gbTypeCodes) = 0 then gb.isNewMark else 1 end)
        and gb.isSilk=(case when LOCATE('isSilk', @gbTypeCodes) = 0 then gb.isSilk else 1 end)
        and gb.isOne=(case when LOCATE('isOne', @gbTypeCodes) = 0 then gb.isOne else 1 end)
        and gb.isPor=(case when LOCATE('isPor', @gbTypeCodes) = 0 then gb.isPor else 1 end)
        and gb.isSpain=(case when LOCATE('isSpain', @gbTypeCodes) = 0 then gb.isSpain else 1 end)
        and gb.isSouthP=(case when LOCATE('isSouthP', @gbTypeCodes) = 0 then gb.isSouthP else 1 end)
        and gb.isRCEP=(case when LOCATE('isRCEP', @gbTypeCodes) = 0 then gb.isRCEP else 1 end) )
    ) b
    inner join client_ApplyA_TZ t on b.ApplyGUID = t.ApplyGUID
    inner JOIN v_Workmain_New a ON b.ApplyGUID=a.ApplyGUID
    inner JOIN client_Corp c ON b.CorpGUID=c.CorpGUID
    inner JOIN admin_WorkQyjw q ON b.QyjwCode = q.QyjwCode
) a
left join code_bmhy h on h.hy_code=a.BigHyCode
order by YwlxName,CountDate desc

2. Optimized SQL

We utilized SQLFlash to rewrite the query, and the optimized SQL is as follows:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
select RowNumber,YwlxName,ItemName,IssueDate,JnName,JwName,lm_name,GbName,Province,CityName,Capital,CapitalC,Register,
(case when BigHyCode='A' then 'Primary Industry' when LOCATE(BigHyCode,';B;C;D;E;')>0 then 'Secondary Industry' else 'Tertiary Industry' end) BigHyCode_Name,
h.hy_name BigHyName,HyName,OpenMode,ManaRange,CurrStatusName,OwnType,CorpType,ZoneName,JnAddress,LinkMan,LinkMobile,LinkPhone,
CorpFax,LinkEmail,(case when item='1' then 'YES' else 'NO' end) item_Name,USCC,OrangeCode,CountDate,ApproveOrder,ApproveNumber,QyjwCode
,MainGUID,ItemCode,YwlxCode,GbCode,HyCode,Currstatus,BigHyCode,item
from (
    select row_number() over (order by a.YwlxCode,b.CountDate desc) as RowNumber,
    a.MainGUID,DATE_FORMAT(b.CountDate,'%Y-%m-%d') as CountDate,a.QyjwCode,q.JwName,a.OpenMode,a.ItemCode,a.ItemName,a.YwlxCode,a.YwlxName,a.GbCode,a.GbName,b.lm_name,a.Province,a.CityName,
    left(ltrim(a.HyCode),1) BigHyCode,a.HyCode,a.HyName,t.ManaRange,b.Capital,b.CapitalC,t.Register,a.JnName,c.CorpType,c.OwnType,c.ZoneName,c.Address JnAddress,c.LinkMan,c.LinkMobile,c.LinkPhone,c.CorpFax,c.LinkEmail,q.Currstatus,q.CurrstatusName,
    c.USCC,c.OrangeCode,a.IssueDate,a.ApproveNumber,a.ApproveOrder,b.item
    from (
        select a.ApplyGUID,a.CorpGUID,a.MainGUID,b1.QyjwCode,DATE_FORMAT(b1.CountDate,'%Y-%m-%d') as CountDate,b1.Capital,b1.CapitalC,b1.item,gb.lm_name
        from (
            select a.QyjwCode,max(a.CountDate) CountDate,sum(IFNULL(Capitalzj,0)) Capital,sum(IFNULL(CapitalCzj,0)) CapitalC,sum((case when a.ItemCode like '%A%' then 1 else 0 end)) as item
            from v_Workmain_New a
            inner JOIN client_ApplyA_TZ t on a.ApplyGUID = t.ApplyGUID
            inner JOIN client_Corp c ON a.CorpGUID=c.CorpGUID
            inner JOIN admin_WorkQyjw q ON a.QyjwCode = q.QyjwCode
            where a.countdate >= @startrq and a.countdate < DATE_ADD(@endrq, INTERVAL 1 DAY)
            and IFNULL(c.MANAGECODE,'') like CONCAT('%',RTRIM(@slzone),'%')
            and IFNULL(c.ZoneCode,'') like CONCAT('%',RTRIM(@zg),'%')
            and IFNULL(c.Address,'') like CONCAT('%',RTRIM(@txtAddr),'%')
            and IFNULL(c.CorpTypeCode,'') like CONCAT('%',RTRIM(@corpType),'%')
            and (@openMode='9' or a.OpenMode=@openMode)
            and (@CurrStatus=9 or q.CurrStatus=@CurrStatus)
            and (@ownTypes='' or LOCATE(c.OwnType,@ownTypes)>0)
            group by a.QyjwCode
        ) b1,v_Workmain_New a,code_bmgb gb
        where a.QyjwCode=b1.QyjwCode and a.CountDate=b1.CountDate and a.GbCode=gb.gb_code
        and IFNULL(a.HyCode,'') like CONCAT('%',RTRIM(@hy),'%')
        and gb.lm_code like CONCAT('%',RTRIM(@lm),'%')
        and a.GbCode like CONCAT('%',RTRIM(@gb),'%')
        and ( gb.isApprove=(case when LOCATE('isApprove',@gbTypeCodes)=0 then gb.isApprove else 1 end)
        and gb.isNewMark=(case when LOCATE('isNewMark',@gbTypeCodes)=0 then gb.isNewMark else 1 end)
        and gb.isSilk=(case when LOCATE('isSilk',@gbTypeCodes)=0 then gb.isSilk else 1 end)
        and gb.isOne=(case when LOCATE('isOne',@gbTypeCodes)=0 then gb.isOne else 1 end)
        and gb.isPor=(case when LOCATE('isPor',@gbTypeCodes)=0 then gb.isPor else 1 end)
        and gb.isSpain=(case when LOCATE('isSpain',@gbTypeCodes)=0 then gb.isSpain else 1 end)
        and gb.isSouthP=(case when LOCATE('isSouthP',@gbTypeCodes)=0 then gb.isSouthP else 1 end)
        and gb.isRCEP=(case when LOCATE('isRCEP',@gbTypeCodes)=0 then gb.isRCEP else 1 end) )
    ) b
    inner join client_ApplyA_TZ t on b.ApplyGUID = t.ApplyGUID
    inner JOIN v_Workmain_New a ON b.ApplyGUID=a.ApplyGUID
    inner JOIN client_Corp c ON b.CorpGUID=c.CorpGUID
    inner JOIN admin_WorkQyjw q ON b.QyjwCode = q.QyjwCode
) a
left join code_bmhy h on h.hy_code=a.BigHyCode
order by YwlxName,CountDate desc

View detailed report

IV. Performance Analysis

SQLFlash Analysis

According to the analysis provided by SQLFlash, the core optimization of this rewrite lies in the SARGable transformation of the date filtering condition. The original query used DATE(a.countdate) >= @startrq and DATE(a.countdate) <= @endrq, applying the DATE() function for type conversion to the countdate column. This compromised the usability of the index, forcing the optimizer to perform a function calculation on each row of data before determining if it met the condition, ultimately triggering a full table scan.

The rewritten query uses a.countdate >= @startrq and a.countdate < DATE_ADD(@endrq, INTERVAL 1 DAY), performing a range comparison directly on the datetime type, thereby eliminating the function call on the column. This makes the query condition SARGable, allowing the optimizer to recognize it as an index-usable range query during the planning phase. In this specific test scenario, the optimizer was able to immediately determine that no rows satisfied the condition (’no matching row in const table’), completely avoiding the overhead of scanning 9,861 rows.

Furthermore, rewriting the nested CASE expression into OR logic (e.g., @openMode='9' or a.OpenMode=@openMode), while logically equivalent, makes it easier for the optimizer to perform short-circuit evaluation and condition pushdown, further enhancing query efficiency.

Original Query Plan

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
mysql> EXPLAIN SELECT ...;
+----+-------------+------------+------------+-------+---------------+---------+---------+------+--------+----------+-------------+
| id | select_type | table      | partitions | type  | possible_keys | key     | key_len | ref  | rows   | filtered | Extra       |
+----+-------------+------------+------------+-------+---------------+---------+---------+------+--------+----------+-------------+
|  4 | DERIVED     | a          | NULL       | ALL   | PRIMARY,...   | NULL    | NULL    | NULL | 9861   | 10.00    | Using where |
|  4 | DERIVED     | t          | NULL       | eq_ref| PRIMARY       | PRIMARY | 202     | ...  | 1      | 100.00   | Using index |
|  4 | DERIVED     | c          | NULL       | eq_ref| PRIMARY       | PRIMARY | 202     | ...  | 1      | 100.00   | Using where |
|  4 | DERIVED     | q          | NULL       | eq_ref| PRIMARY       | PRIMARY | 202     | ...  | 1      | 10.00    | Using where |
+----+-------------+------------+------------+-------+---------------+---------+---------+------+--------+----------+-------------+

(2077.75s)

Optimized Query Plan

1
2
3
4
5
6
7
mysql> EXPLAIN SELECT ...;
+----+-------------+------------+------------+-------+---------------+---------+---------+------+--------+----------+--------------------------------+
| id | select_type | table      | partitions | type  | possible_keys | key     | key_len | ref  | rows   | filtered | Extra                          |
+----+-------------+------------+------------+-------+---------------+---------+---------+------+--------+----------+--------------------------------+
|  4 | DERIVED     | NULL       | NULL       | NULL  | NULL          | NULL    | NULL    | NULL | NULL   | NULL     | no matching row in const table|
+----+-------------+------------+------------+-------+---------------+---------+---------+------+--------+----------+--------------------------------+
(12.29s)

Performance Metrics Comparison

Metric	Original Query	Optimized Query
SQL WHERE Condition	`DATE(column) >= @param`	`column >= @param`
Execution Time	0.0105s	0.0024s
Performance Improvement	—	77.12%
Query Cost	2077.75	12.29
Cost Reduction	—	99.4%
Innermost Rows Scanned	9,861 rows (ALL)	0 rows (Short-circuit)
Intermediate Table Rows	98 rows	2 rows
SARGable	No (Function on column)	Yes (Direct comparison)
Index Utilization	Not usable	Fully utilized

Analysis of Optimization Principles

SARGable Condition Rewriting

The original query applied the DATE() function to the countdate column, causing the following issues:

Index Invalidity: Function calculation on the column prevented the optimizer from using the idx_CountDate index for a range scan.
Full Table Scan: It was forced to read all 9,861 rows and execute the DATE() function on each row before filtering.
CPU Intensive: Each row required date type conversion calculations.

After rewriting it to a direct comparison:

Index Usable: Conditions act directly on the column, allowing the optimizer to leverage the B-Tree index for quick positioning.
Early Short-Circuit: The optimizer can determine no matching rows during the planning phase, completely skipping the scan.
Zero Calculation Overhead: No need to perform function calculations on any rows.

The SARGable rewrite is the root cause of the performance improvement, transforming the query from a full table scan to an index-optimized short-circuit query.

V. Conclusion

This case fully validates the significant optimization value of SARGable condition rewriting in complex multi-table join queries, providing a reusable methodology and practical pattern for SQL performance optimization in similar scenarios.

Enforce the SARGable Principle: Conditions in the WHERE clause involving indexed columns should avoid applying functions to the column; prioritize rewriting them into a SARGable form.
Standard Pattern for Date Queries: For date range queries, consistently use the pattern column >= start AND column < DATE_ADD(end, INTERVAL 1 DAY).
Execution Plan Monitoring: Regularly check slow query logs and pay attention to the “ALL” scan type and high cost values in EXPLAIN results, prioritizing the optimization of these queries.
Simplify CASE Expressions: Rewrite nested CASE WHEN ... THEN column ELSE value END constructs into forms like @param='default' OR column=@param.
Complementary Index Strategy: Create single-column indexes on date columns frequently used for range queries; consider composite indexes for multiple conditional combinations.

What is SQLFlash?

SQLFlash is your AI-powered SQL Optimization Partner.

Based on AI models, we accurately identify SQL performance bottlenecks and optimize query performance, freeing you from the cumbersome SQL tuning process so you can fully focus on developing and implementing business logic.