Optimizing Existence Checks: Replace COUNT(*) with SELECT 1 LIMIT 1

When a query only needs to determine whether records exist (a binary yes/no) based on certain conditions, many developers still write SELECT count(*) … and then check if the returned number is greater than zero.

Current Common Practice

Both beginners and seasoned developers tend to use count for this purpose.

During many code reviews we encounter the following pattern: the business code needs to know only if at least one row satisfies the condition, not how many rows there are. The typical SQL and Java code look like this:

#### SQL写法:
SELECT count(*) FROM table WHERE a = 1 AND b = 2

#### Java写法:
int nums = xxDao.countXxxxByXxx(params);
if ( nums > 0 ) {
  //当存在时，执行这里的代码
} else {
  //当不存在时，执行这里的代码
}

It looks fine and works, but it forces the database to count all matching rows.

Optimization Proposal

A more efficient way is to ask the database to return a single row as soon as it finds one, using SELECT 1 … LIMIT 1. The Java side can then simply test for a non‑null result.

#### SQL写法:
SELECT 1 FROM table WHERE a = 1 AND b = 2 LIMIT 1

#### Java写法:
Integer exist = xxDao.existXxxxByXxx(params);
if ( exist != NULL ) {
  //当存在时，执行这里的代码
} else {
  //当不存在时，执行这里的代码
}

The SQL no longer uses the count function; instead it uses LIMIT 1, causing the query to stop after the first matching row is found.

In the business code you only need to check whether the result is non‑null.

Conclusion

The larger the original result set, the more noticeable the performance improvement, and in some cases it can also reduce the need for composite indexes.

If you found this useful, feel free to share it.