Cleanest way to check if a record exists

Question

I am using the following code to check if a row exists in my database:

$sql = "SELECT COUNT(1) FROM myTable WHERE user_id = :id_var";
$stmt = $conn->prepare($sql);
$stmt->bindParam(':id_var', $id_var);
$stmt->execute();

if ($stmt->fetch()[0]>0)
{
    //... many lines of code
}

All of the code works and the doubts I have are concerning if the previous code is clean and efficient or if there is room for improvement.

Currently there are two questions bugging me with my previous code:

Should I have a LIMIT 1 at the end of my SQL statement? Does COUNT(1) already limit the amount of rows found by 1 or does the server keep searching for more records even after finding the first one?
The if ($stmt->fetch()[0]>0). Would this be the cleanest way to fetch the information from the SQL Query and execute the "if conditional"?

Of course if anyone spots anything else that can improve my code, I would love your feedback.

Regarding SQL `SELECT` Query..You should take a look at this : http://stackoverflow.com/questions/1676551/best-way-to-test-if-a-row-exists-in-a-mysql-table — Umair Shah, May 07 '16 at 22:36
Instead of `if ($stmt->fetch()[0]>0){...`, you could do, `list($count) = $stmt->fetch(PDO::FETCH_NUM); if ($count > 0){...`. Much more cleaner approach. — Rajdeep Paul, May 07 '16 at 22:39
@RajdeepPaul Interesting. Good alternative. And do you happen to have an opinion regarding the `LIMIT 1` issue? — Webeng, May 07 '16 at 22:47
I would *think* that if user_id is a unique key, `LIMIT 1` wouldn't make a difference. — mukunda, May 07 '16 at 22:48
@Webeng `LIMIT 1` won't do any good if you're doing `COUNT(*)` or `COUNT(1)` with your `SELECT` statement, because anyway when you do `SELECT COUNT(1) ...`, it will return only one row. — Rajdeep Paul, May 07 '16 at 22:50
@UmairShahYousafzai very nice thread. It seems that `SELECT EXISTS(SELECT 1 FROM test2 WHERE id ='321321' LIMIT 1)` might be a faster query than using `COUNT(1)` — Webeng, May 07 '16 at 22:50

score 3 · Accepted Answer · answered May 07 '16 at 22:50

Q: Should I have a LIMIT 1 at the end of my SQL statement? Does COUNT(1) already limit the amount of rows found by 1 or does the server keep searching for more records even after finding the first one?

Your SELECT COUNT() FROM query will return one row, if the execution is successful, because there is no GROUP BY clause. There's no need to add a LIMIT 1 clause, it wouldn't have any affect.

The database will search for all rows that satisfy the conditions in the WHERE clause. If the user_id column is UNIQUE, and there is an index with that as the leading column, or, if that column is the PRIMARY KEY of the table... then the search for all matching rows will be efficient, using the index. If there isn't an index, then MySQL will need to search all the rows in the table.

It's the index that buys you good performance. You could write the query differently, to get a usable result. But what you have is fine.

Q: Is this the cleanest...

  if ($stmt->fetch()[0]>0)

My personal preference would be to avoid that construct, and break that up into two or more statements. The normal pattern...separate statement to fetch the row, and then do a test.

Personally, I would tend to avoid the COUNT() and just get a row, and test whether there was a row to fetch...

  $sql = "SELECT 1 AS `row_exists` FROM myTable WHERE user_id = :id_var";
  $stmt = $conn->prepare($sql);
  $stmt->bindParam(':id_var', $id_var);
  $stmt->execute();
  if($stmt->fetch())  {
      // row found
  } else {
      // row not found 
  }
  $stmt->closeCursor();

Would the `closeCursor()` function only be necessary if I had other sql statements already prepared and ready to be executed? or would it make a difference in the situation that other sql statements are being prepared further below? — Webeng, May 07 '16 at 22:57
If user_id is not unique in myTable, then add a LIMIT 1 to the query in my answer. There's no need to transfer more than one row to the client. I add the stmt close out of habit. With some databases/drivers, it's not required. But with the MySQL driver under PDO, I think the close is required if you are going to execute another prepared statement. Also, for this case, I would prefer to use bindValue instead of bindParam, just because the value of $id_var is determined when we do the prepare. We;d only see a difference if we did `$id_var = someval;` after the prepare and before the execute. — spencer7593, May 07 '16 at 23:12

Cleanest way to check if a record exists

1 Answers1