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

When developers need to verify whether at least one record satisfies certain conditions, the common pattern is to execute SELECT count(*) FROM table WHERE a = 1 AND b = 2 and then check if the returned count is greater than zero. This approach scans all matching rows even though only the existence of a single row is required.

Current Typical Implementation

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) {
    // execute when record exists
} else {
    // execute when no record
}

While functional, this method unnecessarily processes all matching rows.

Optimized Solution

Replace the count query with a lightweight existence check that stops after finding the first matching row:

SQL

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

Java

Integer exist = xxDao.existXxxxByXxx(params);
if (exist != NULL) {
    // execute when record exists
} else {
    // execute when no record
}

Here the COUNT function is removed and replaced by LIMIT 1, causing the database to return as soon as it finds a single matching row, avoiding further scanning.

In the business code, you only need to check for a non‑null (or non‑zero) result to determine existence.

Conclusion

Using SELECT 1 … LIMIT 1 instead of SELECT count(*) can significantly improve performance, especially when the condition matches many rows, and may also reduce the need for additional composite indexes.