How Do You Determine the Number of Rows Returned by a MySQL Query?

How to Determine the Number of Rows Returned by a MySQL Query

In the realm of MySQL queries, it is often necessary to ascertain the number of rows that a specific query yields. This count provides valuable insights into the size and content of the result set. Let's explore various methods to achieve this.

Iterative Row Counting

The straightforward approach is to iterate through the result rows and increment a counter, but this method can be inefficient for large datasets.

mysql_num_rows() Function

For those working with the MySQL client library, the mysql_num_rows() function provides a direct way to retrieve the row count in a result set. Its equivalent in PHP is mysqli_num_rows().

COUNT(*) Function

To count the number of rows matching specific criteria, utilize the COUNT(*) function within a query. For instance:

<code class="sql">SELECT COUNT(*) FROM foo WHERE bar= 'value';</code>

SQL_CALC_FOUND_ROWS and FOUND_ROWS()

To determine the total rows in a query that employs a LIMIT clause, use SQL_CALC_FOUND_ROWS in the initial query followed by SELECT FOUND_ROWS():

<code class="sql">SELECT SQL_CALC_FOUND_ROWS * FROM foo
   WHERE bar="value" 
   LIMIT 10;

SELECT FOUND_ROWS();</code>

Additional Considerations

When dealing with large tables, using SQL_CALC_FOUND_ROWS can be inefficient. For such cases, consider executing a separate count query and caching the result for future reference.

Deprecation of SQL_CALC_FOUND_ROWS

As of MySQL version 8.0.17, the SQL_CALC_FOUND_ROWS function is deprecated and will be removed in future versions. The recommended approach is to issue a separate query to calculate the row count.

The above is the detailed content of How Do You Determine the Number of Rows Returned by a MySQL Query?. For more information, please follow other related articles on the PHP Chinese website!