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

When a query only needs to know whether any rows satisfy a condition—i.e., a binary "has" or "has not" result—many developers still write SELECT count(*) FROM table WHERE ... , even though the actual count is irrelevant.

Typical Current Practice

Both newcomers and seasoned programmers often use a COUNT query in SQL and then check the returned number in Java:

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

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

Optimized Approach

Instead of counting, query for a single row and stop as soon as one is found. The SQL becomes:

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

In Java, call a method that returns a non‑null value when a row exists:

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

Using LIMIT 1 prevents the database from scanning the entire result set after finding the first matching row, which reduces I/O and CPU usage, especially on large tables, and can lessen the need for additional composite indexes.

Summary

The performance gain grows with the number of rows that would otherwise be counted; in many cases the optimized query is noticeably faster and simpler.