MySQL - How to count rows before pagination?

Question

I am making a search page to find users. I have que query to find them and actually I can do the pagination with "LIMIT startRow, numberRows". But how could I count the total number of "registers" found before doing the pagination? I would like to add at my search page, the number of the users found in a search.

I need something like: "Page 1 of 100". I actually I have "Page 1" but I don't know how to count the total of results before paginate.

¿Maybe could be necesary execute an extra query with "SELECT COUNT(*)"? ¿Is there another way to count before pagination to avoid another query?

I use two sql queries, one for single word, and another for multiword:

Base sql query (for single word and multi word search):

"SELECT * FROM accounts AS A INNER JOIN profiles AS P ON A.account_id = P.account_id "

Single word condition:

"WHERE A.username LIKE ? OR P.name LIKE ? OR P.name LIKE ? OR P.surname LIKE ? OR P.surname LIKE ? LIMIT ?,?"

Multi word condition:

"WHERE CONCAT(P.name, ' ', P.surname) LIKE ? LIMIT ?,?"

Thanks a lot.

Filtrate

Is there some reason you can't use displaytag? – Vidovic

I didn't know about that library. Seems interesting for another search method that I'll need. Despite of this I would like to know about to count the total results before pagination, 'cause I'm learning. Thank you. – Filtrate

Answer 1

Modify the query like this:

SELECT SQL_CALC_FOUND_ROWS * FROM accounts ... LIMIT ...

This will return the same limited/offset result as before (the first page of results, for example), but if you then immediately send this second query to the server...

SELECT FOUND_ROWS();

The server will return a count of the total number of rows that would have been returned if the LIMIT had not been imposed on the previous query. There's your total number of results.

This will, of course, mean that your initial query takes longer, because the optimizer can't take any shortcuts that might allow it to stop evaluating rows once the LIMIT is satisfied, but in some cases, no such shortcuts are available anyway.

This is the "official" mechanism for doing what you are trying to accomplish.

http://dev.mysql.com/doc/refman/5.7/en/information-functions.html#function_found-rows

Kerrikerrie

Finally! It works as I was specting. A small query without consuming much resources with "like" filters. Thanks for your answer. – Filtrate

Important note: SQL_CALC_FOUND_ROWS is deprecated in MySQL 8.0 and it will be removed in the next version of MySQL – Zorazorah

Answer 2

You could use do something like this:

SELECT (select count(*) from produtos) as counter, column1, column2, column3, column4 FROM accounts AS A INNER JOIN profiles AS P ON A.account_id = P.account_id WHERE A.username LIKE ? OR P.name LIKE ? OR P.name LIKE ? OR P.surname LIKE ? OR P.surname LIKE ? LIMIT ?,?;

Instead of selecting all (*), you use each column name, and create another column called counter.

Propylaeum

It only shows the total number of entries in database omitting the "WHERE" filter. If I hange it to "SELECT COUNT(*) AS total, column2, ..." it shows the number of rows that match with the "WHERE" filter, but the result of mysql only have one row that is the first of the query and it also have the "total" new colum that in fact, have the number that I want, the number of rows before pagination. Thanks for your answer. – Filtrate

What about this? SELECT (select count(*) from accounts AS A INNER JOIN profiles AS P ON A.account_id = P.account_id WHERE A.username LIKE ? OR P.name LIKE ? OR P.name LIKE ? OR P.surname LIKE ? OR P.surname LIKE ?) as counter, column1, column2, column3, column4 FROM accounts AS A INNER JOIN profiles AS P ON A.account_id = P.account_id WHERE A.username LIKE ? OR P.name LIKE ? OR P.name LIKE ? OR P.surname LIKE ? OR P.surname LIKE ? LIMIT ?,? Kind of big query, that can be improved, but I hope it helps :) – Propylaeum