Does WHERE 1=1 Affect Index Usage in MySQL? An Empirical Investigation

A colleague asked why developers sometimes write WHERE 1=1 in SQL, prompting an investigation into its impact on query performance, especially regarding index usage in MySQL when using MyBatis conditional tags.

<select id="queryBookInfo" parameterType="com.ths.platform.entity.BookInfo" resultType="java.lang.Integer">
  select count(id) from t_book t where 1=1
  <if test="title !=null and title !='' ">
    AND title = #{title}
  </if>
  <if test="author !=null and author !='' ">
    AND author = #{author}
  </if>
</select>

The code shows a typical MyBatis mapper that uses WHERE 1=1 to simplify conditional appending of filters. Some claim this pattern harms performance by disabling index usage.

To verify, the author executed EXPLAIN SELECT * FROM t_book WHERE title = '且在人间'; and

EXPLAIN SELECT * FROM t_book WHERE 1=1 AND title = '且在人间';

. Both plans displayed the same possible_keys and key, confirming that MySQL’s optimizer removes the redundant 1=1 condition and still uses the index.

Conclusion: the WHERE 1=1 construct does not prevent index usage and has negligible impact on query efficiency because MySQL optimizes it away. However, for large datasets the extra parsing step may still add overhead.

As a best practice, the author suggests using MyBatis’s <where> tag to handle conditional clauses more cleanly:

<select id="queryBookInfo" parameterType="com.ths.platform.entity.BookInfo" resultType="java.lang.Integer">
  select count(*) from t_book t
  <where>
    <if test="title !=null and title !='' ">
      title = #{title}
    </if>
    <if test="author !=null and author !='' ">
      AND author = #{author}
    </if>
  </where>
</select>

This approach lets MyBatis generate the appropriate WHERE clause without the need for the dummy 1=1 condition.