Get the total limit in mysql using the same query?

Question

I use the pagination method, which I did: The first query will read all the results, and the second query will perform the usual selection using LIMIT

Is there technically any way to do this that I did, but with only one request?

What I have now:

SELECT count(*) from table SELECT * FROM table LIMIT 0,10 

Answer 1

No one mentions this, but the correct way to use the SQL_CALC_FOUND_ROWS method is as follows:

• Complete your query: SELECT SQL_CALC_FOUND_ROWS * FROM `table` LIMIT 0, 10
• Then run this query immediately after: SELECT FOUND_ROWS() . The result of this query contains the full score of the previous query, i.e. As if you did not use the LIMIT . This second query is fast because the result is already cached.

Answer 2

You can do this with a subquery:

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

But I do not think this has any advantage.

edit: As Ilya said, the total counter and the lines have a completely different meaning, there is no real goal in getting this data in the same query. I will stick with two requests. I simply gave this answer, showing that it is possible, and not that it is a good idea.

Answer 3

While I saw some bad approaches to this, when I considered it earlier, there were two generally accepted solutions:

• Running your query and then doing the same query with count , as you did in your question.

• Run the query and run it again using the SQL_CALC_FOUND_ROWS .

eg. SELECT SQL_CALC_FOUND_ROWS * FROM table LIMIT 0,10

This second approach is how phpMyAdmin does it.

Answer 4

You can run the first query, and then the second query, and you will get both the score and the first results.

The query returns a set of records. An account, of course, is not one of the records that the query "SELECT *" can return to, because there is only one account for the entire set of results.

In any case, you did not say in which programming language you are running these SQL queries and which interface you are using. Perhaps this parameter exists in the interface.

Answer 5

SELECT SQL_CALC_FOUND_ROWS your query here Restriction ...

Without starting any other queries or destroying the session, then do SELECT FOUND_ROWS ();

And you will get the total number of rows

Answer 6

The problem with 2 queries is consistency. Between two requests, the data may be changed. If your logic depends on what is counted should be returned, then your only approach is to use SQL_CALC_FOUND_ROWS.

Starting with Mysql 8.0.17, SQL_CALC_FOUND_ROWS and FOUND_ROWS () will be deprecated. I do not know a consistent solution after this functionality has been removed.