Optimizing Existence Checks: Replace COUNT(*) with SELECT 1 LIMIT 1 in SQL and Java

When a query only needs to know whether a record exists ("has" or "has not"), many developers still write SELECT count(*) and then check the numeric result in code.

Typical current approach

Developers often write SQL like:

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

and call a DAO method such as:

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

Optimized solution

Instead of counting rows, use a query that stops after the first matching row:

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

In Java, retrieve a single value and test for non‑null:

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

The LIMIT 1 clause makes the database return as soon as it finds one matching row, avoiding the overhead of counting all matches and potentially reducing the need for composite indexes.

Conclusion

Replacing COUNT(*) with SELECT 1 LIMIT 1 yields noticeable performance gains, especially when the condition matches many rows, and can simplify index design.