Check if there are at least 2 rows for a query in MySQL

Question

Is there a better way to check if there are at least two rows in a table for a given condition?

Please look at this PHP code:

$result = mysql_query("SELECT COUNT(*) FROM table WHERE .......");
$has_2_rows = (mysql_result($result, 0) >= 2);

The reason I dislike this, is because I assume MySQL will get and count ALL the matching rows, which can be slow for large results.

I would like to know if there is a way that MySQL will stop and return "1" or true when two rows are found. Would a LIMIT 2 at the end help?

Thank you.

Answer 1

This is a good question, and yes there is a way to make this very efficient even for large tables.

Use this query:

SELECT count(*) FROM (
    SELECT 1
    FROM table
    WHERE .......
    LIMIT 2
) x

All the work is done by the inner query, which stops when it gets 2 rows. Also note the select 1, which gives you a tiny bit more efficiency, since it doesn't have to retrieve any values from columns - just the constant 1.

The outer count(*) will count at most 2 rows.

Note: Since this is an SQL question, I've omitted PHP code from the answer.

Answer 2

The query below will only inspect two rows as you request. mysql_num_rows can check how many rows are returned without any looping.

$result = mysql_query("SELECT col1 FROM t1 WHERE ... LIMIT 2");
if (mysql_num_rows($result) == 2) {

---

Please avoid using ext/mysql and switch to PDO or mysqli if you can.