Why COUNT(*) Is Unnecessary: Optimize Existence Checks with LIMIT 1

When a query only needs to know whether a record exists (yes or no), many developers still write SELECT count(*) to get the number of matching rows, even though the actual count is irrelevant.

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

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

Optimized solution

Replace the count query with a simple 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 ) {
  // when exists, execute this code
} else {
  // when not exists, execute this code
}

Here the SQL no longer uses count; instead it uses LIMIT 1 so the database returns as soon as it finds a single row, avoiding unnecessary scanning.

In business code you can simply check for a non‑null (or non‑zero) result to determine existence.

Conclusion

The fewer rows the database has to process, the more noticeable the performance improvement, and in some cases it can even reduce the need for composite indexes.