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

Many developers check for the existence of rows by executing SELECT count(*) FROM table WHERE a = 1 AND b = 2 and then testing the returned count in Java, even though they only need a true/false result.

SELECT count(*) FROM table WHERE a = 1 AND b = 2

Typical Java usage:

int nums = xxDao.countXxxxByXxx(params);
if (nums > 0) {
    // code when rows exist
} else {
    // code when rows do not exist
}

While this works, it forces the database to count all matching rows, which can be wasteful.

Optimized approach

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

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

Corresponding Java code:

Integer exist = xxDao.existXxxxByXxx(params);
if (exist != null) {
    // code when rows exist
} else {
    // code when rows do not exist
}

Here the SQL no longer uses count; instead it uses LIMIT 1 so the database returns as soon as it finds a single row, and the Java side simply checks for a non‑null value.

Conclusion

The fewer rows the database needs to examine, the greater the performance gain; in many cases this technique also reduces the need for composite indexes.