Home > Database > Mysql Tutorial > body text

How to Count Total Rows While Using LIMIT in MySQL?

Patricia Arquette
Release: 2024-11-12 00:02:03
Original
821 people have browsed it

How to Count Total Rows While Using LIMIT in MySQL?

Counting Rows with SELECT and LIMIT in MySQL

In MySQL, the LIMIT clause can be used to restrict the number of rows returned by a query. However, when using LIMIT, it can be challenging to also obtain the total number of rows in the table that satisfy the query conditions.

Consider the following query, which retrieves the first 10 rows from a join between two tables, table1 and table2, based on certain conditions (cond1, cond2, ..., condN):

SELECT A.ID, A.NAME, B.ID, B.NAME
FROM table1 A
JOIN table2 B ON ( A.ID = B.TABLE1_ID )
WHERE
    cond1, cond2, ..., condN
LIMIT 10
Copy after login

To retrieve the total number of rows that satisfy these conditions without issuing another query without LIMIT, MySQL provides the SQL_CALC_FOUND_ROWS keyword. By incorporating SQL_CALC_FOUND_ROWS into the initial query, MySQL calculates the total number of rows before applying the LIMIT clause:

SELECT SQL_CALC_FOUND_ROWS A.ID, A.NAME, B.ID, B.NAME
FROM table1 A
JOIN table2 B ON ( A.ID = B.TABLE1_ID )
WHERE
  cond1, cond2, ..., condN
LIMIT 10
Copy after login

After executing this query, the total row count can be obtained using the following separate query:

SELECT FOUND_ROWS();
Copy after login

By combining these two queries, you can both limit the number of rows returned and retrieve the total row count, without the need for an additional query.

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

source:php.cn
Statement of this Website
The content of this article is voluntarily contributed by netizens, and the copyright belongs to the original author. This site does not assume corresponding legal responsibility. If you find any content suspected of plagiarism or infringement, please contact admin@php.cn
Latest Articles by Author
Popular Tutorials
More>
Latest Downloads
More>
Web Effects
Website Source Code
Website Materials
Front End Template