So what IS the best way to check if a row exists? EXISTS, COUNT or num_rows?

Question

If your only goal is to check if a row exists in php (true or false), what is the best way to do it?

Option 1?

$result = mysql_query("SELECT * FROM users WHERE id = '1'");
$num_rows = mysql_num_rows($result);

if ($num_rows == 1) 
    // one user, like it should be.

else 
  // do something else

Option 2?

$query = mysql_query("select count(1) from users where id = 1")

if (mysql_result($query, 0) == 1)
    // one user, like it should be.

else 
  // do something else

Option 3?

$query = mysql_query("something like SELECT EXISTS( SELECT */1/COUNT(*)/etc. ...)")

if (mysql_result($query, 0) == 1)
    // one user, like it should be.

else 
  // do something else

Beter option 4?

you name it.

Subquestions

COUNT(*), COUNT(1) or COUNT(id)?

possible duplicate of [SQL getting row count optimization](http://stackoverflow.com/questions/8852753/sql-getting-row-count-optimization) — mario, Jan 29 '12 at 11:59
@mario: It's related but not the same. Here it is asked to find out if count=0 or >0, not the exact count. — ypercubeᵀᴹ, Jan 29 '12 at 12:01
@ypercube: The other duplicates linked there cover that. And more exist on the COUNT vs EXISTS issue, http://stackoverflow.com/questions/1676551/best-way-to-test-if-a-row-exists-in-a-mysql-table — mario, Jan 29 '12 at 12:06
@mario: Thanks for the link. But, how would you get the result using php? It is not JUST about the mysql, also about the php part. — SuperSpy, Jan 29 '12 at 12:11
@SuperSpy: The `SELECT EXISTS (subquery)` needs different treatment in PHP as it will always return one row, with either 1 or 0 (True or False). — ypercubeᵀᴹ, Jan 29 '12 at 12:46

score 5 · Accepted Answer · answered Jan 29 '12 at 14:08

5

Option 3 is the fastest way to check if a row exists if you are using MySQL:

$query = mysql_query("SELECT EXISTS(SELECT 1 FROM users WHERE id = 1)")

if (mysql_result($query, 0) == 1)
    // one user, like it should be.

else 
  // do something else

answered Jan 29 '12 at 14:08

trebormf

3,156
3
25
21

score 3 · Answer 2 · answered Jan 29 '12 at 12:35

I think the question refers more the code itself then the time involved, so using his query:

$result = mysql_query("SELECT * FROM users WHERE id = '1'");
//if result not returned(false) from Mysql return False Else True
//This is just example and you can do anything you need in side the if()
if(!$result) {
//or return some error or redirect to another piece of code
return FALSE;
} else {
//or do some other php/mysql magic
//if there is a result you have the row to work with of needed
return TRUE;
}

mysql_query
...excerpt from PHP manual Return Values

For SELECT, SHOW, DESCRIBE, EXPLAIN and other statements returning resultset, mysql_query() returns a resource on success, or FALSE on error.

For other type of SQL statements, INSERT, UPDATE, DELETE, DROP, etc, mysql_query() returns TRUE on success or FALSE on error.

ypercubeᵀᴹ · Answer 3 · 2012-01-29T12:04:37.727

1

The EXISTS is faster then SELECT COUNT(*) because the subquery will stop searching when it finds one row. It won't have to find them all and count them. It will return either 0 or 1:

SELECT EXISTS 
       ( SELECT * FROM ... )

edited Jan 29 '12 at 12:04

answered Jan 29 '12 at 11:56

ypercubeᵀᴹ

113,259
19
174
235

I am correct in understanding that I will still need to use `mysql_result`? And same question here: any advantage in using '`SELECT COUNT(*) ...`', '`SELECT COUNT(1) ...`' or '`SELECT COUNT(id) ...`'? – SuperSpy Jan 29 '12 at 12:05
The link by mario explains the differences. `COUNT(*)` is usually faster than `COUNT(NotNullColumn)`. `COUNT(NullableColumn)` yields differnet result. – ypercubeᵀᴹ Jan 29 '12 at 12:06
general rule on any rdbms is that EXISTS is the fastest way to go, not just MySQL – Cruachan Jan 29 '12 at 14:44

So what IS the best way to check if a row exists? EXISTS, COUNT or num_rows?

3 Answers3

Related