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 *

FROM 
       individual i
WHERE 
       i.hid=:hhid
AND 
       i.hihh=:answer";

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

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.

up vote 3 down vote accepted

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).

Your Answer

 
discard

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.

Not the answer you're looking for? Browse other questions tagged or ask your own question.