CODEWITHSUNDEEP
phpmysqlsql
ashofphoenix
ashofphoenix

Reputation: 155

using count instead of mysql_num_rows?

What is the difference between(performance wise) 

$var = mysql_query("select * from tbl where id='something'");

$count = mysql_num_rows($var);

if($count > 1){

    do something

}

and

$var = mysql_query("select count(*) from tbl where id='somthing'");

P.S: I know mysql_* are deprecated.

Upvotes: 0

Views: 954

Answers (7)

Ivo
Ivo

Reputation: 5420

select * is asking mysql to fetch all data from that table (given the conditions) and give it to you, this is not a very optimizable operation and will result in a lot of data being organised and sent over the socket to PHP.

Since you then do nothing with this data, you have asked mysql to do a whole lot of data processing for nothing.

Instead, just asking mysql to count() the number of rows that fit the conditions will not result in it trying to send you all that data, and will result in a faster query, especially if the id field is indexed.

Overall though, if your php application is still simple, while still being good practice, this might be regarded as a micro-optimization.

Upvotes: 1

mohamed nur
mohamed nur

Reputation: 331

select * from tbl where id='something' selects all the data from table with ID condition.

The COUNT() function returns the number of rows that matches a specified criteria.

For more reading and practice and demonstration please visit =>>> w3schools

Upvotes: 0

Gaurav Porwal
Gaurav Porwal

Reputation: 513

SELECT * FROM tbl_university_master;

2785 row(s) returned

Execution Time : 0.071 sec

Transfer Time : 7.032 sec

Total Time : 8.004 sec

SELECT COUNT(*) FROM tbl_university;

1 row(s) returned

Execution Time : 0.038 sec

Transfer Time : 0 sec

Total Time : 0.039 sec

Upvotes: 1

Jordan
Jordan

Reputation: 3022

I think using 

$var = mysql_query("select count(*) from tbl where id='somthing'");

Is more efficient because you aren't allocating memory based on the number of rows that gets returned from MySQL.

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269803

The first version returns the entire result set. This can be a large data volume, if your table is wide. If there is an index on id, it still needs to read the original data to fill in the values.

The second version returns only the count. If there is an index on id, then it will use the index and not need to read in any data from the data pages.

If you only want the count and not the data, the second version is clearer and should perform better.

Upvotes: 3

Sherlock
Sherlock

Reputation: 7597

The first collects all data and counts the number of rows in the resultset, which is performance-intensive. The latter just does a quick count which is way faster.

However, if you need both the data and the count, it's more sensible to execute the first query and use mysql_num_rows (or something similar in PDO) than to execute yet another query to do the counting.

And indeed, mysql_* is to be deprecated. But the same applies when using MySQLi or PDO.

Upvotes: 0

Laurent S.
Laurent S.

Reputation: 6946

I would use the second for 2 reasons :

  • As you stated, mysql_* are deprecated
  • if your table is huge, you're putting quite a big amount of data in $var only to count it.

Upvotes: 1

Related Questions