COUNT(*) OR SELECT to find a single result

[php_man]

Generally, what is the most efficient way to get a Yes/No answer as to whether there is at least 1 result to a query:

$db->query("SELECT COUNT(*) AS total FROM x WHERE Y");
$result = $db->get_row();
if ($result['total'] > 0) {
...
} else {

}

or:

$db->query("SELECT j AS y FROM x WHERE Y LIMIT 1");
if ($db->num_rows()) {
...
} else {

}

Does it make any difference? My guess is the 2nd is more efficient because it only scans one row, can a COUNT(*) be made to only scan for 1 row?

[r937]

whether there is at least one result for a query depends on the query

if you search a million row accounting table for an out-of-balance condition, you may or may not get a result back, but you still have to scan a million rows

whether there is at least one row in a table is a different question, and count(*) is fastest for that

[php_man]

I am talking about running it on table to find a matching row. Generally the criteria will be on a single indexed column.

[r937]

if the query has a WHERE clause with a condition that involves an indexed column, and if your second query (the one using LIMIT, not count(*)) selects only that column, then i really don't know which will be faster

test it yourself on your own data, run it both ways, compare the execution plans (EXPLAIN) and compare the elapsed and cpu time differences