Using count(*) vs num_rows

Question

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?

Answer 1

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.

Answer 2

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.

Answer 3

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

Answer 4

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.