Optimizing Existence Checks: Use SELECT 1 LIMIT 1 Instead of COUNT(*)

Hello everyone, I'm Lei Ge.

When you need to check whether records exist based on one or more conditions, many developers write SELECT count(*) FROM table WHERE ... even though they only care about existence, not the exact count.

Common practice

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) {
    // code when exists
} else {
    // code when not exists
}

This works but is not optimal.

Optimization

Replace the count query with a limit‑1 query and a Java method that checks for null:

SQL

SELECT 1 FROM table WHERE a = 1 AND b = 2 LIMIT 1

Java

Integer exist = xxDao.existXxxxByXxx(params);
if (exist != NULL) {
    // code when exists
} else {
    // code when not exists
}

Using LIMIT 1 stops the database after finding the first matching row, avoiding a full count scan and potentially reducing the need for composite indexes.

Conclusion

The fewer rows the query has to examine, the greater the performance gain; in some cases this also reduces the need to create multi‑column indexes.