Why Replace COUNT(*) with SELECT 1 LIMIT 1 for Faster Existence Checks

Current Common Practice

Many developers check for the existence of records by counting rows, even when they only need to know whether at least one row matches the criteria.

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

Typical Java code mirrors this pattern:

int nums = xxDao.countXxxxByXxx(params);
if (nums > 0) {
    // execute when a record exists
} else {
    // execute when no record exists
}

Optimized Approach

Instead of counting, query for a constant value and stop after the first match using LIMIT 1. This avoids scanning all matching rows.

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

Corresponding Java code can use a DAO method that returns a non‑null value when a row is found:

Integer exist = xxDao.existXxxxByXxx(params);
if (exist != NULL) {
    // execute when a record exists
} else {
    // execute when no record exists
}

Using LIMIT 1 makes the database stop searching after the first matching row, which can significantly improve performance, especially when many rows satisfy the condition, and may reduce the need for additional composite indexes.