CODEWITHSUNDEEP
mysqllimit
Renato Souza de Oliveira
Renato Souza de Oliveira

Reputation: 4584

Two selects in one query

I have a product table.

I'm going to create a pagination on my frontend. So I need to do this select:

select * from `product` limit 20 offset 0

I also need the total number of records in the table of products (I'll show the number of pages). I have this query:

select count(*) as all from `product`

I wanted to run these two queries in just one query.

Something like:

select *, count(*) as total from `hospedes` limit 20 offset 0

the above query does not work. is there any way to do this?

Upvotes: 0

Views: 45

Answers (2)

Bill Karwin
Bill Karwin

Reputation: 562961

I recommend you run two queries. One to get the total COUNT(*) from the table, and then run a separate query for your pagination.

In theory you can do this:

mysql> SELECT SQL_CALC_FOUND_ROWS * FROM tbl_name
    -> WHERE id > 100 LIMIT 10;
mysql> SELECT FOUND_ROWS();

This promises to let you get the information of how many tables match the query as if you had not used LIMIT. Read https://dev.mysql.com/doc/refman/5.7/en/information-functions.html#function_found-rows for details

It does give you the correct count, but using this method usually causes a high performance cost.

Read https://www.percona.com/blog/2007/08/28/to-sql_calc_found_rows-or-not-to-sql_calc_found_rows/ for a test that shows it is a better strategy to run separate queries. Admittedly, that blog is from 2007 and the test was performed on MySQL 5.0. You can try to reproduce their test on the current version of MySQL to see if it has improved since then.

Upvotes: 1

Joe T
Joe T

Reputation: 2350

I don't believe you can run this as one query unless you are willing to give up the "LIMIT" and just retrieve all records, do a count on the result set, and throw away all but the first 20.

Upvotes: 1

Related Questions