How to determine the "number of pages" most effectively?

Question

Hi!

I hope I'm not writing a duplicate, but I haven't found anything that answers my question. (Although it seems to me to be quite common problem.)

The problem occurs in nearly every web project: You have a table with many many entries and want them to be displayed on single pages.

Now I wonder what's the best way to compute the number of pages needed for a certain set of table rows.

Here some approaches I've been thinking of. I'd like to get some response on how effective they are. I'll give PHP-specific examples, but I bet there are similar techniques in other languages.

1. The probably best way is to save the number of pages statically and modify the value every time a new entry is added. (Nevertheless... I'm looking for a dynamic solution :-) )

2. Do a SELECT COUNT(*) over the rows of interest and compute the page number every time the page is displayed.

3. Do a ordinary select to get a result set for all rows. Now don't load the rows by calling mysql_fetch_row or so, but get the number of rows with mysql_num_rows. (Since I have no idea how this is implemented I cannot say whether it is effective or not. Anyone who knows?) Then I could comfortably move the result set pointer. (For mysqli there is mysql_data_seek, but the native MySQL extension has no similar function. Therefore I assume that this is just some buffering behaviour of mysqli)

Can anyone say how to count the number of rows (number of pages) most effectively?

Answer 1

If you really wanted to something of truely WTF proportions, you could always keep track of how many rows there were by incrementing some register using a trigger on insert/delete.

But I think this is an answer asking for yet more questions ;).

Just use SELECT COUNT if you have to. If its slow, that means your database is built wrong usually.

Also, I have the feeling there is premature optimisation creeping in here. Don't optimise prematurely. Make it make sense, and then make it make sense better. Make sense?

Answer 2

If I understand correctly, in most systems I've worked on I've needed the number of pages at the same time as I am grabbing a new set of rows for a page. This allows a display of "page N of M" or something like that as the title, and a screen full of entries.

It's database dependent, but usually you pass in the current_page number (or row number), then do a "limited query" (fetchsize, top, etc.) to get the next set of rows and a total page number. Granted it gets ugly if the number of rows is changing underneath, but I usually don't worry about that.

Paul.

Answer 3

Number 2 is the most common pattern

select count(*) from [Table] where [Expressions]

And then

select [Columns] from [Table] where [Expressions] limit [Pagesize] offset [Pagenum*Pagesize-Pagesize]

This gives you the total rows for the entire result set, but only the data of the rows for the current page.

Many frameworks or CMSes have conventions for caching certain parts of this data in the session which you may or may-not want to do depending on you expected table sizes, volatility of data, etc.

Answer 4

I think you want to build your navigation around a single SQL query which only modifies the LIMIT keyword. For example SELECT * FROM myTable LIMIT 0,20 will return the first 20 rows. In your PHP script for page 2 you will then set the SQL query to be SELECT * FROM myTable LIMIT 20,40 thus returning rows from 20 to 40. Is this what you were looking for?

The navigation can be built with your PHP script by simply doing a select COUNT(*) / rowsPerPage and thus looping a structure of code until you meet the total number of rows. The end query would then LIMIT to lastLimit,TotalRowCount.

Edit: to compute how many pages you need to compute all rows you would need to define how many rows 1 page can have. You would then do TotalRows/MaxPerPage = howManyPagesNeeded

Answer 5

2 questions to consider in this context:

• how to handle data that gets updated/inserted while you are viewing?
• how many concurrent users will be browsing the data, and will the application be able to keep up with the traffic when it fetches data pages dynamically?

Depending on the answers, some of the other answers here may or may not apply.

Answer 6

Actually, if you're filling a webpage with PHP, it's easier to just get the MySQL table, put the data into an array and use a for-loop to go throught the data like this

for($i=0;$i<count($arrayvar);$i++){}

With even a simple template engine, you can declare a block inside another block and parse all the data in one consequtive line.

Hope that's the type of answer you're looking for. I'm not really clear on what you mean by your question, but I think this is it.

Answer 7

You might want to check out this question, it is very similar to what you are asking.

Answer 8

A nice way to do this is use SQL_CALC_FOUND_ROWS

Example:

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

Even though you're limiting the query to 10 results, the second query will return the actual total rows that would have been returned had you not used a limit.

It's important to note that FOUND_ROWS() is not reliable when using MySQL's database replication