MYSQL Count all rows and pagination of data using range/limit

Question

I dont know if this is a duplicate but here's my question..

I was trying to implement a pagination of data taken from database

My dilemma is:

Should i go about pagination, querying data in groups, ie. 5 (doing a Select with limits/ range), then displaying them in a table with pagination. It will have page numbers so it would require counting all the table entries thus that would be 2 database queries for the initial display.or
query all the row entries and then count the result set and apply pagination. This would remove the query count on the database but would also mean that i would download the whole data everytime a single view on any page is made (but i could also apply caching)

and other suggestions are highly accepted and thank you in advance

Wesley van Opdorp · Accepted Answer · 2011-05-30 06:51:44Z

7

SQL_CALC_FOUND_ROWS .

This will allow you to use LIMIT and have the amount of rows as no limit was used.

answered May 30, 2011 at 6:51

Wesley van Opdorp

15k4 gold badges43 silver badges59 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Salman Arshad Over a year ago

Since you cannot do a mysql_query("query 1; query 2") you need to execute the two queries one by one.

Wesley van Opdorp Over a year ago

This is one query, FOUND_ROWS() is not a query to the database. Internally it still does 2 lookups, but there is no performance loss then doing query with limit, and an separate count query.

Salman Arshad Over a year ago

from within PHP, this function still needs to be called just like a query, like this:

mysql_query("SELECT SQL_CALC_FOUND_ROWS * FROM foobar LIMIT 0, 10"); echo array_shift(mysql_fetch_assoc(mysql_query("SELECT FOUND_ROWS()")));

-- two mysql_query although this may not be the same as two database queries.

Nader Over a year ago

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

Collectives™ on Stack Overflow

MYSQL Count all rows and pagination of data using range/limit

1 Answer 1

4 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related