CODEWITHSUNDEEP
mysql
rustytoaster21
rustytoaster21

Reputation: 85

How to get count of rows and rows in one query?

Let's say I have a black box query that I don't really understand how it works, something along the lines of:

SELECT ... FROM ... JOIN ... = A (denoted as A)

Let's say A returns 500 rows.

I want to get the count of the number of rows (500 in this case), and then only return a limit of 50. How can I wrote a query built around A that would return the number '500' and 50 rows of data?

Upvotes: 0

Views: 128

Answers (3)

Bill Karwin
Bill Karwin

Reputation: 562348

This is what SELECT SQL_CALC_FOUND_ROWS is intended to do.

SELECT SQL_CALC_FOUND_ROWS * FROM tbl_name WHERE id > 100 LIMIT 10;

SELECT FOUND_ROWS();

The first query returns the limited set of rows.

The second query calls FOUND_ROWS() which returns an integer number of how many rows matched the most recent query, the number of rows which would have been returned if that query had not used LIMIT.

See https://dev.mysql.com/doc/refman/8.0/en/information-functions.html#function_found-rows

However, keep in mind that using SQL_CALC_FOUND_ROWS incurs a significant performance cost. Benchmarks show that it's usually faster to just run two queries:

SELECT COUNT(*) FROM tbl_name WHERE id > 100; -- the count of matching rows

SELECT * FROM tbl_name WHERE id > 100 LIMIT 10; -- the limited result

See https://www.percona.com/blog/2007/08/28/to-sql_calc_found_rows-or-not-to-sql_calc_found_rows/

Upvotes: 1

Connor Butch
Connor Butch

Reputation: 688

There are a few ways you can do this (assuming that I am understanding your question correctly). You can open run two queries (and point a cursor to each) and then open and return both cursors, or you can run a stored procedure in which the count query is ran first, the result is stored into a variable, then it is used in another query.

Let me know if you would like an example of either of these

Upvotes: 0

GMB
GMB

Reputation: 222472

You can use window functions (available in MySQL 8.0 only) and a row-limiting clause:

select a.*, count(*) over() total_rows
from ( < your query >) a
order by ??
limit 50

Note that I added an order by clause to the query. Although this is not technically required, it is a best practice: without an order by clause where the column (or set of columns) uniquely identifies each row, it is undefined which 50 rows the database will return, and the results may not be consistent over consecutive executions of the same query.

Upvotes: 1

Related Questions