Where should I do the rowcount when checking for existence: sql or php?

Question

In the case when I want to check, if a certain entry in the database exists I have two options.

I can create an sql query using COUNT() and then check, if the result is >0...

...or I can just retrieve the record(s) and then count the number of rows in the returned rowset. For example with $result->num_rows;

What's better/faster? in mysql? in general?

Answer 1

YMMV, but I suspect that if you are only checking for existence, and don't need to use the retrieved data in any way, the COUNT() query will be faster. How much faster will depend on how much data.

Answer 2

The fastest is probably asking the database if something exists:

SELECT EXISTS ([your query here])

Answer 3

I would definitely do it in the PHP to decrease load on the database.

In order to get a count and get the returned rows in SQL you would have to do two queries.. a COUNT and then a SELECT

The PHP way gives you everything you need in one result object.

Answer 4

SELECT COUNT(*) FROM table

is the best choice, this operation is extremely fast both on small tables and large tables. While it's possible that

SELECT id FROM table

is faster on small tables, the difference in speed will be microscopic. But if you have a large table, this operation can be very slow.

Therefore, your best bet is to always choose to COUNT(*) the table (and it's faster to do * than it is to pick a specific column) as overall, it will be the fastest operation.

Answer 5

If all you are doing is checking for the existance, then

Select count(*) ...

But if you will retrieve the data if it exists, then just get the data and check it in PHP, otherwise you'll have two calls.

Answer 6

For me is in the database.

Making a count(1) is faster than $result->num_rows because in the $result->num_rows you make 2 operations 1 select and a count if the select has a count is faster to get the result. Except if you also want the information from the db.

Answer 7

SELECT 1 
  FROM (SELECT 1) t 
 WHERE EXISTS( SELECT * FROM foo WHERE id = 42 )

Just tested, works fine on MySQL v5

COUNT(*) is generally less efficient if:

1. you can have duplicates (because the DBMS will have to exhaustively search all of the records/indexes to give you the exact answer) or
2. have NULL entries (for the same reason)

If you are COUNT'ing based on a WHERE clause that is guaranteed to produce a single record (or 0) and the DBMS knows this (based upon UNIQUE indexes), then it ought to be just as efficient. But, it is unlikely that you will always have this condition. Also, the DBMS may not always pick up on this depending on the version and DBMS.

Counting in the application (when you don't need the row) is almost always guaranteed to be slower/worse because:

1. You have to send data to the client, the client has to buffer it and do some work
2. You may bump out things in the DBMS MRU/LRU data cache that are more important
3. Your DBMS will (generally) have to do more disk I/O to fetch record data that you will never use
4. You have more network activity

Of course, if you want to DO something with the row if it exists, then it is definitely faster/best to simply try and fetch the row to begin with!

Answer 8

If you want raw speed, benchmark! In addition to the methods others have suggested:

SELECT 1 FROM table_name WHERE ... LIMIT 1

may be faster due to avoiding the subselect. Benchmark it.