Understanding the Impact of WHERE 1=1 on MySQL Index Usage in MyBatis Queries

Hello everyone, I am Peng Lei.

A new colleague asked what "where 1=1" means, so I will explain it.

where 1=1

First, consider the following MyBatis XML snippet:

<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 counts rows that match optional conditions. In MyBatis the if tags add a condition only when the corresponding parameter is non‑null, and the leading where 1=1 prevents syntax errors when the first condition is omitted.

Many people claim that using where 1=1 can cause performance problems or make indexes unusable. We test this claim with MySQL's EXPLAIN command.

Experiment

With an indexed title column we run:

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

and

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

The output shows that both queries use the same possible_keys and key, meaning the index is still applied.

Conclusion

The condition where 1=1 does not prevent index usage; MySQL's optimizer removes the redundant true predicate during query planning. However, for very large data sets it is still better to let MyBatis generate the WHERE clause automatically.

A cleaner MyBatis mapping can be written using the <where> tag:

<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>

We replace the manual where 1=1 with the <where> tag.