Having the following queries, where the first query is returning a field of number of rows found, and the second returns the actual rows:
SELECT count(*) as total FROM individual i WHERE i.hid=:hhid AND i.hihh=:answer
$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
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.
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.
INDEX(hid, hihh) (in either order) would help performance. Having two one-column indexes is not as good: