MySQL Query can we count rows using php to reduce time of count(*) in a query or there is no difference?

Posted on

Question :

Having the following queries, where the first query is returning a field of number of rows found, and the second returns the actual rows:

First Query:

SELECT count(*) as total FROM individual i WHERE i.hid=:hhid AND i.hihh=:answer

Second Query:

$sql = "SELECT *

       individual i

$exec = $conn->prepare($checkHeadOfHH);
$exec->bindValue(':hhid', $hhid);
$exec->bindValue(':answer', $answer);

I want to know if the parser when a query contains count(), do SELECT * FROM.. first then count the rows ? If yes, so basically, is better to use the second approach for better performance and less execution time ?

I am asking this question, because of having huge database and we always run count(*) queries to check data.

Answer :

First one will be best. The server will only return a number in the first case. In the second it will return a table.

You can get better performance by creating indexes for the columns you are using in the where clause.

Responses to your replies via comments

Creating indexes the way I did in the second query?

No. Creating an index is something you do on MySQL, the database management system. The second query copies a table from MySQL to PHP, and then PHP counts the records. The first query let MySQL do the counting. That is way more efficient.

Look at this link to see how to create indexes.

Shoveling the rows from MySQL to PHP costs more than letting MySQL do the work.

Adding INDEX(hid, hihh) (in either order) would help performance. Having two one-column indexes is not as good: INDEX(hid), INDEX(hihh).

Leave a Reply

Your email address will not be published. Required fields are marked *