Optimizing Existence Checks in SQL: Use SELECT 1 LIMIT 1 Instead of COUNT(*)

When a query only needs to know whether any rows satisfy certain conditions, many developers write SELECT count(*) FROM table WHERE a = 1 AND b = 2 and then check if the returned number is greater than zero. This pattern appears both in raw SQL and in Java code that calls a DAO method returning the count.

Although this works, it forces the database to count all matching rows, which can be wasteful when only the existence of at least one row matters.

A more efficient alternative is to ask the database to return a single row as soon as it finds a match, for example: SELECT 1 FROM table WHERE a = 1 AND b = 2 LIMIT 1 In Java, the corresponding DAO method can be renamed to something like existXxxxByXxx and return a non‑null value (e.g., an Integer) when a row exists:

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

By replacing COUNT(*) with LIMIT 1, the database stops scanning as soon as it finds the first matching row, which can significantly improve performance, especially on large tables or when the condition matches many rows. In some cases it also reduces the need for composite indexes.

In summary, using a constant select with LIMIT 1 for existence checks yields better performance and simpler code.