Anne Schwarz Anne Schwarz - 3 days ago 6
MySQL Question

PHP Mysqli - SELECT * or SELECT COUNT(*) when result is 0?

Is there any difference in performance between

SELECT *
and
SELECT COUNT(*)
when no rows will be found?

My chat script check every second for new messages so it would be good to know if I need count(*)

What would be faster and better for the server:

$result = mysqli_query($con, "blablabla");

if(mysqli_num_rows($result) > 0)
{

}


OR

$result = mysqli_query($con, "blablabla");
$menge = mysqli_fetch_row($result);
$menge = $menge[0];

if($menge > 0)
{

}


When no rows will be found?! I'm asking that for AJAX chat and most of the time there will be no new rows.

Answer

With a basic query the time difference would be negligible between the 2 (well within natural variation when executing a query repeatedly). With a complex query there could be a significant difference as likely with just a COUNT you could use a simplified query.

For example if the full query needs a sub query to get the latest details for a grouped set then this could make the full query quite slow. However the count might only be interested in whether there is a row (rather than the details of the latest row) and so not require the sub query. This could make a major difference.

Assuming you had an index on the id and the value you are using is at or close to the max value (ie, you are not expecting to return the full 1m records, rather a couple max from the last few seconds) then I wouldn't expect a significant difference caused by the amount of data returned.

The big constant performance hits are making the connection to MySQL, and the basic processing of a query by the database (ie, processing the request. not performing the query). These provide a fairly constant overhead for any query, and quite a significant one for a simple query which is fast once it is executed. Because of this the overhead from performing 2 queries sometimes (when new rows have been added) is likely to dwarf the negligible saving from just getting a count unless there are new records.

Comments