Efficient use of MySQL pages and queries

Question

I am currently running two MySQL queries to handle my pagination ...

Query 1 selects all rows from a table, so I know how many pages I need to list.

Query 2 selects rows for the current page (for example, rows 0 to 19 (LIMIT 0, 19) for page 1, rows 20-39 for the second page, etc., etc.).

This seems to be a waste of two repeated requests, with the only difference being that it is part of the LIMIT.

What would be the best way to do this?

Should I use PHP to filter the results after running a single query?

Edit: Should I run a single query and use something like array_slice () to list only the rows that I want?

Answer 1

The best and fastest way is to use 2 MYSQL queries for pagination (as you are already using), in order to avoid more headaches, you should simplify the query used to determine the total number of rows by selecting only one column (say, the primary key). SELECT * FROM sampletable WHERE condition1>1 AND condition2>2 to split into such a query you can use these two queries SELECT * FROM sampletable WHERE condition1>1 AND condition2>2 LIMIT 0,20 SELECT id FROM sampletable WHERE condition1>1 AND condition2>2

Answer 2

Use FOUND_ROWS()

The SELECT statement may include a LIMIT clause to limit the number of rows that the server returns to the client. In some cases, it is desirable to know how many lines the operator would have to return without LIMIT, but without restarting the operator. To get this row counter, enable the SQL_CALC_FOUND_ROWS option in the SELECT statement, and then call FOUND_ROWS ():

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

The second SELECT returns a number indicating how many rows will be returned by the first SELECT if it were written without the LIMIT clause.

Please note that this puts an additional burden on the database, as it must determine the size of the full result set each time. Use SQL_CALC_FOUND_ROWS only when you need it.