MySQL - how to count rows before pagination?

Question

I am doing a search page to find users. I have a qu query to find them, and in fact I can paginate using "LIMIT startRow, numberRows". But how can I calculate the total number of "registers" found before doing pagination? I would like to add to my search page the number of users found in the search.

I need something like: "Page 1 of 100". Actually, I have “Page 1”, but I don’t know how to calculate the total number of results before paginate.

¿You may need to complete an additional query with "SELECT COUNT (*)"? ¿Is there any other way of counting before pagination to avoid another query?

I use two sql queries, one for one word and the other for verbose:

Basic sql query (to search by one word and several words):

"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 ?,?" 

The state of a wordy word:

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

Thank you very much.

Answer 1

Modify the query as follows:

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

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

 SELECT FOUND_ROWS(); 

The server will return a counter for the total number of rows that would be returned if LIMIT not superimposed on the previous request. There is your total number of results.

This, of course, means that your initial request takes longer because the optimizer cannot accept any shortcuts that may allow it to stop evaluating strings after performing LIMIT , but in some cases such shortcuts are still available.

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

Answer 2

You can use 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.