Exploring the Impact of Using “WHERE 1=1” in MySQL

My article is open to everyone; non-member readers can click this link to read the full text.

>> Introduction

Recently, while using MyBatis for SQL scripting in a project, I came across the practice of starting a WHERE clause with “WHERE 1=1” to simplify the concatenation of multiple conditions. Here’s an example to discuss this technique and whether “WHERE 1=1” has any impact on performance.

<select id="" parameterType="">
    SELECT * FROM users 
    WHERE 1=1
        <if test="userName != null">
            AND user_name = #{userName}
        </if>
        <if test="userAge != null">
            AND user_age = #{userAge}
        </if>
        <if test="userSex != null">
            AND user_sex = #{userSex}
        </if>
</select>

>> Discussing Two Common Practices for Concatenating Conditions in MyBatis

Using “WHERE 1=1”

As illustrated in the introduction:

<select id="" parameterType="">
    SELECT * FROM users 
    WHERE 1=1
        <if test="userName != null">
            AND user_name = #{userName}
        </if>
        <if test="userAge != null">
            AND user_age = #{userAge}
        </if>
        <if test="userSex != null">
            AND user_sex = #{userSex}
        </if>
</select>

Using the <where> Tag

MyBatis offers a <where> tag that inserts a WHERE clause only if there's at least one condition that evaluates to true. If there are no valid statements before an AND or OR, the <where> element removes them.

<select id="" parameterType="">
    SELECT * FROM users 
    <where>
        <if test="userName != null">
            AND user_name = #{userName}
        </if>
        <if test="userAge != null">
            AND user_age = #{userAge}
        </if>
        <if test="userSex != null">
            AND user_sex = #{userSex}
        </if>
    </where>
</select>

>> Impact on Performance

The performance difference between using “WHERE 1=1” and the <where> tag lies in the optimization of SQL query performance for the former and in the dynamic generation of SQL statements for the latter. Let's analyze this in detail:

MySQL Version:

SELECT VERSION(); -- 5.7.44

Data Setup SQL:

CREATE TABLE IF NOT EXISTS users
(
  id INT NOT NULL AUTO_INCREMENT PRIMARY KEY COMMENT 'Auto-increment ID',
  name VARCHAR(20) COMMENT 'Name',
  age TINYINT COMMENT 'Age'
) ENGINE = INNODB;

INSERT INTO users (name, age) VALUES ('John', 18), ('Jane', 19), ('Doe', 20), ('Smith', 21);

“WHERE 1=1”:

In versions above 5.7, SQL query optimization will eliminate the “1=1” part, not affecting indexing. However, it’s noted that lower versions might experience some impact, so it’s worth paying attention.

EXPLAIN SELECT * FROM users WHERE 1=1 AND name = 'John';
SHOW WARNINGS;

The optimized SQL demonstrates that the “1=1” part is optimized away by the query optimizer, thus not significantly impacting overall performance.

Performance Comparison:

-- With "WHERE 1=1" AND a condition
SELECT * FROM users WHERE 1=1 AND name = 'John';
-- Execution Time: 0.046s

-- With just "WHERE 1=1"
SELECT * FROM users WHERE 1=1;
-- Execution Time: 0.046s

“<where>” Tag:

Compared to “WHERE 1=1”, which is handled by the MySQL server’s query optimizer, the <where>tag processes dynamic SQL construction. However, it doesn’t significantly impact performance, as the underlying dynamic SQL generation isn’t overly complex.

Conclusion

Both where 1=1and the <where> tag are common practices for concatenating conditions, with no significant impact on performance. The choice between them can be based on team standards. Moreover, these approaches’ performance is unaffected by the volume of data processed, as each execution is handled just once, so there’s no performance difference even with large datasets.

Stackademic

Thank you for reading until the end. Before you go:

• Please consider clapping and following the writer! 👏
• Follow us X | LinkedIn | YouTube | Discord
• Visit our other platforms: In Plain English | CoFeed | Venture