Effective use of pagination and MySQL queries

Question

At the moment I run 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 the rows for the current page (e.g: rows 0 to 19 (LIMIT 0, 19) for page 1, rows 20-39 for page two etc etc).

It seems like a waste of two duplicate queries with the only difference being the LIMIT part.

What would be a better way to do this?

Should I use PHP to filter the results after one query has been run?

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

Answer 1

The best & fastest way is to use 2 MYSQL queries for pagination (as you are already using), to avoid over headache you must simplify the query used to find out the total number of rows by selecting only one column (say the primary key) that's enough. SELECT * FROM sampletable WHERE condition1>1 AND condition2>2 for paginating such a query you may 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