3

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:

  1. 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

  2. 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

ianace
  • 1,646
  • 2
  • 17
  • 31

1 Answers1

7

SQL_CALC_FOUND_ROWS .

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

Wesley van Opdorp
  • 14,888
  • 4
  • 41
  • 59
  • 1
    Since you cannot do a `mysql_query("query 1; query 2")` you need to execute the two queries one by one. – Salman A May 30 '11 at 07:39
  • 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. – Wesley van Opdorp May 30 '11 at 08:00
  • 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. – Salman A Jun 02 '11 at 07:58
  • Important note: SQL_CALC_FOUND_ROWS is deprecated in MySQL 8.0 and it will be removed in the next version of MySQL – Nader Mar 13 '19 at 17:10