CODEWITHSUNDEEP
phpmysql
Manish
Manish

Reputation: 1972

Using count(*) vs num_rows

To get number of rows in result set there are two ways:

  1. Is to use query to get count

    $query="Select count(*) as count from some_table where type='t1'";

    and then retrieving the value of count.

  2. Is getting count via num_rows(), in php.

so which one is better performance wise?

Upvotes: 10

Views: 9257

Answers (5)

user268396
user268396

Reputation: 11976

There are a few differences between the two:

  1. num_rows is the number of result rows (records) received.
  2. count(*) is the number of records in the database matching the query.

The database may be configured to limit the number of returned results (MySQL allows this for instance), in which case the two may differ in value if the limit is lower than the number of matching records. Note that limits may be configured by the DBA, so it may not be obvious from the SQL query code itself what limits apply.

Using num_rows to count records implies "transmitting" each record, so if you only want a total number (which would be a single record/row) you are far better off getting the count instead.

Additionally count can be used in more complex query scenario's to do things like sub-totals, which is not easily done with num_rows.

Upvotes: 4

Michael Brook
Michael Brook

Reputation: 380

It depends on your implementation. If you're dealing with a lot of rows, count(*) is better because it doesn't have to pass all of those rows to PHP. If, on the other hand, you're dealing with a small amount of rows, the difference is negligible.

Upvotes: 1

Rahul
Rahul

Reputation: 1181

num_rows() would be better if you have small quantity of rows and count(*) will give you performance if there are large number of rows and you have to select one and send it to php.

Upvotes: 0

Explosion Pills
Explosion Pills

Reputation: 191749

If your goal is to actually count the rows, use COUNT(*). num_rows is ordinarily (in my experience) only used to confirm that more than zero rows were returned and continue on in that case. It will probably take MySQL longer to read out many selected rows compared to the aggregation on COUNT too even if the query itself takes the same amount of time.

Upvotes: 7

Matthew Mcveigh
Matthew Mcveigh

Reputation: 5685

count is much more efficient both performance wise and memory wise as you're not having to retrieve so much data from the database server. If you count by a single column such as a unique id then you can get it a little more efficient

Upvotes: 2

Related Questions