How to Efficiently Count Rows in MySQL Query Results?

Counting Rows in MySQL Query Results

Introduction:

When working with MySQL databases, counting the number of rows resulting from a query is a common task for various applications. This article provides several methods for efficiently counting rows in MySQL.

1. Using the mysql_num_rows() Function:

The mysql_num_rows() function returns the number of rows in a result set. To use it, retrieve the result from the database using a query and then pass the result to the function:

# Example usage in PHP:
$result = mysqli_query($conn, "SELECT * FROM table1");
$num_rows = mysqli_num_rows($result);

2. Using the COUNT(*) Keyword:

The COUNT(*) keyword allows you to count the number of rows that match a specific criterion. This is particularly useful for filtering results:

# Count rows where 'bar' equals 'value':
SELECT COUNT(*) FROM table1 WHERE bar = 'value';

3. Getting Total Rows with LIMIT (Until MySQL 8.0.16):

To obtain the total number of rows without considering the LIMIT clause, use the SQL_CALC_FOUND_ROWS keyword followed by SELECT FOUND_ROWS():

# Calculate total rows ignoring LIMIT:
SELECT SQL_CALC_FOUND_ROWS * FROM table1 WHERE bar = 'value' LIMIT 10;

SELECT FOUND_ROWS();

Important Note for MySQL 8.0.17 and Later:

SQL_CALC_FOUND_ROWS is deprecated in MySQL 8.0.17 and will be removed in future versions. Instead, it is recommended to use a separate query to calculate the total row count.

The above is the detailed content of How to Efficiently Count Rows in MySQL Query Results?. For more information, please follow other related articles on the PHP Chinese website!