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

When checking whether records exist based on certain conditions, many developers write SELECT count(*) FROM table WHERE a=1 AND b=2 and then test the returned number in Java.

This approach works but forces the database to count all matching rows, which is unnecessary when only existence matters.

The recommended optimization is to query SELECT 1 FROM table WHERE a=1 AND b=2 LIMIT 1 and in Java check whether the result is non‑null, e.g.,

Integer exist = xxDao.existXxxxByXxx(params); if (exist != null) { … } else { … }

.

Using LIMIT 1 stops the scan after the first matching row, reducing I/O and CPU consumption, especially on large tables, and can even avoid creating extra composite indexes.

In summary, replacing COUNT(*) with a limited existence query yields noticeable performance gains for existence checks.