Why SELECT 1 LIMIT 1 Outperforms COUNT(*) for Existence Checks

Common Practice

Many developers, both newcomers and veterans, use COUNT(*) to determine whether any rows satisfy certain conditions, even when the exact number of rows is irrelevant.

SELECT count(*) FROM table WHERE a = 1 AND b = 2;
int nums = xxDao.countXxxxByXxx(params);
if (nums > 0) {
    // code when rows exist
} else {
    // code when no rows exist
}

Optimization

A more efficient approach is to select a constant value and stop after the first matching row using LIMIT 1, then simply test for a non‑null result in the application code.

SELECT 1 FROM table WHERE a = 1 AND b = 2 LIMIT 1;
Integer exist = xxDao.existXxxxByXxx(params);
if (exist != NULL) {
    // code when rows exist
} else {
    // code when no rows exist
}

Using LIMIT 1 lets the database return as soon as it finds a single row, avoiding the overhead of counting all matching rows.

Conclusion

The performance gain becomes more noticeable as the number of matching rows increases, and in some cases it can even reduce the need for composite indexes.