9

I am making a pagination method, what i did was: First query will count all results and the second query will do the normal select with LIMIT

Is there technically any way to do this what I've done, but with only one query?

What I have now:

SELECT count(*) from table
SELECT * FROM table LIMIT 0,10
Fusseldieb
  • 1,324
  • 2
  • 19
  • 44
TDSii
  • 1,675
  • 4
  • 20
  • 29

6 Answers6

14

No one really mentions this, but the correct way of using the SQL_CALC_FOUND_ROWS technique is like this:

  1. Perform your query: SELECT SQL_CALC_FOUND_ROWS * FROM `table` LIMIT 0, 10
  2. Then run this query directly afterwards: SELECT FOUND_ROWS(). The result of this query contains the full count of the previous query, i.e. as if you hadn't used the LIMIT clause. This second query is instantly fast, because the result has already been cached.
silkfire
  • 24,585
  • 15
  • 82
  • 105
3

You can do it with a subquery :

select 
    *,
    (select count(*) from mytable) as total
from mytable LIMIT 0,10

But I don't think this has any kind of advantage.

edit: Like Ilya said, the total count and the rows have a totally different meaning, there's no real point in wanting to retrieve these data in the same query. I'll stick with the two queries. I just gave this answer for showing that this is possible, not that it is a good idea.

krtek
  • 26,334
  • 5
  • 56
  • 84
  • This will return the result of `count(*)` multiple times, it makes no sense writing such a query. The sad thing is that I know a few programmers who would have done it this way without blinking an eye. – Ilya Kogan Mar 11 '11 at 23:18
  • it's the reason I said there's no advantage to do things like this. But if there's some kind of requirement doing it all in one query, why not, this won't hurt performance that badly. – krtek Mar 11 '11 at 23:22
  • 2
    @Krtek You are not doing 1 query there but 2. A subquery or a normal query is here the same thing. So why not just do 2 queries to make it better. A requirement for 1 liners doesnt make any sense here :P. Still you have the only solution tho :-P – Michael Koper Mar 11 '11 at 23:27
  • 2
    @Michael We can be picky about what is 1 query, from a connection point of view, this will need only one connection to the database server. From the database point of view, sure the subquery is a query. – krtek Mar 11 '11 at 23:30
  • @TDSii the only performance hit will be the size of the data, the added column with the size on each row. Mysql is smart enough to compute the subquery only once. It it's not the case you can do the same thing with a cross join just to be sure. It is even possible that computation time will be shorter than sending two queries. – krtek Mar 11 '11 at 23:35
3

While i've seen some bad approaches to this, when i have looked into this previously there were two commonly accepted solutions:

  1. Running your query and then running the same query with a count as you have done in your question.

  2. Run your query and then run it again with the SQL_CALC_FOUND_ROWS keyword.

eg. SELECT SQL_CALC_FOUND_ROWS * FROM table LIMIT 0,10

This second approach is how phpMyAdmin does it.

Jarod Elliott
  • 15,460
  • 3
  • 35
  • 34
2

You can run the first query and then the second query, and so you'll get both the count and the first results.

A query returns a set of records. The count is definitely not one of the records a "SELECT *" query can return, because there is only one count for the entire result set.

Anyway, you didn't say what programming language you run these SQL queries from and what interface you're using. Maybe this option exists in the interface.

Ilya Kogan
  • 21,995
  • 15
  • 85
  • 141
2

SELECT SQL_CALC_FOUND_ROWS your query here Limit ...

Without running any other queries or destroying the session then run SELECT FOUND_ROWS();

And you will get the total count of rows

kenitech
  • 1,151
  • 12
  • 17
1

The problem with 2 queries is consistency. In the time between the 2 queries the data may be changed. If your logic depends on what is counted has to be returned then your only approach would be to use SQL_CALC_FOUND_ROWS.

As of Mysql 8.0.17 SQL_CALC_FOUND_ROWS and FOUND_ROWS() will be deprecated. I don't know of a consistent solution after this functionality has been removed.

Daniel
  • 99
  • 1
  • 2