Clever Geek Handbook

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

In the event that I want to check if a specific record exists in the database, I have two options.

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

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

Which is better / faster? in mysql? generally?

+1


source to share


8 answers 


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

Just tested, works great on MySQL v5

COUNT (*) is generally less efficient if:

  • you may have duplicates (because the DBMS will have to exhaustively search all records / indexes to give you an exact answer) or
  • have NULL records (for the same reason)


If you are doing COUNT'ing based on a WHERE clause that is guaranteed to create one record (or 0), and the DBMS knows this (based on UNIQUE indexes), then it should be just as efficient. But it is unlikely that you will always have this condition. In addition, the DBMS may not always recognize this depending on the version and the DBMS.

In-app counting (when you don't need a string) is almost always guaranteed to be slower / worse because:

  • You have to send data to the client, the client has to buffer it and do some work.
  • You may run into problems in the MRU / LRU DBMS data cache, which are more important
  • Your DBMS will (generally) have to do more disk I / O to get write data that you will never use
  • You have more online activity.

Of course, if you want to do something with the string, if it exists, then it is definitely faster / better to just try and get the string to start with!

+1


source


YMMV, but I suspect that if you are only checking for existence and do not have to use the retrieved data in any way, the COUNT () request will be faster. How much faster will depend on how much data you have.



+2


source


The fastest is probably querying the database if something exists:

SELECT EXISTS ([your query here])
+2


source


If all you do is check for existence, then

Select count(*) ...

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

+1


source


For me there is in the database.

Creating counter (1) is faster than $ result-> num_rows because in $ result-> num_rows you do 2 operations 1 and select the counter if the selection has a counter faster to get the result. Also, if you also want to get information from db.

+1


source 


SELECT COUNT(*) FROM table

- the best choice, this operation works very quickly on both small tables and large tables. Although it is possible that

SELECT id FROM table

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, it is best to choose COUNT(*) the table

(and faster to do *

than to choose a specific one column

), as usual, it will be the fastest operation.

0


source


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

SELECT 1 FROM table_name WHERE ... LIMIT 1

may be faster due to subquery exclusion. Appreciate this.

0


source


I would definitely do this in PHP to reduce the load on the database.

To get the counter and get the rows returned in SQL, you will need to execute two queries: COUNT and then SELECT

The PHP way gives you everything you need in a single result object.

-1


source






More articles:


All Articles