I really hope I am not writing a replica, however i haven't found something that solutions my question. (Even though it appears in my experience to become very common problem.)
The issue happens in virtually every web project: You've got a table with lots of many records and wish these to be shown on single pages.
Now I question what's the easiest method to compute the amount of pages required for a particular group of table rows.
Here some approaches I have been considering. Let me acquire some response how effective they're. I'll give PHP-specific good examples, however i wager you will find similar approaches to other languages.
The most likely easiest way would be to save the amount of pages statically and customize the value whenever a new entry is added. (Nonetheless... I am searching for an engaged solution :-) )
SELECT COUNT(*)within the rows of great interest and compute the page number each time the page is displayed.
Perform a regular choose to obtain a result looking for all rows. Now don't load the rows by calling
mysql_fetch_rowapproximately, but get the amount of rows with
mysql_num_rows. (Since I Have have no clue how this really is implemented I am unable to say whether it's effective or otherwise. Anybody you never know?) I Quickly could easily slowly move the result set pointer. (For
mysql_data_seek, however the native MySQL extension doesn't have similar function. Well, I think that case some loading behavior of
Can anybody say how you can count the amount of rows (quantity of pages) most effectively?
Number Two is easily the most common pattern
select count(*) from [Table] where [Expressions]
select [Columns] from [Table] where [Expressions] limit [Pagesize] offset [Pagenum*Pagesize-Pagesize]
This provides the total rows for the whole result set, only the information from the rows for that current page.
Many frameworks or CMSes have conventions for caching song of the data within the session which you might or may-not wish to accomplish based on you would expect table dimensions, unpredictability of information, etc.
Should you wanted to something of actually WTF proportions, you can always keep an eye on the number of rows there have been by incrementing some register utilizing a trigger on place/remove.
However I think it is really an answer requesting yet more questions ).
Only use Choose COUNT if you need to. If it is slow, which means your database is made wrong usually.
Also, I've the sensation there's premature optimisation sneaking in here. Don't optimise prematurely. Allow it to be seem sensible, and then suggest it seem sensible better. Seem sensible?
Basically understand properly, in many systems I have done I have needed the amount of pages simultaneously like me getting a brand new group of rows for any page. This enables a presentation of "page N of M" or something like that like this because the title, along with a screen filled with records.
It's database dependent, truly you pass in the presentweb page number (or row number), then perform a "limited query" (fetchsize, top, etc.) to obtain the next group of rows along with a total page number. Granted it will get ugly if the amount of rows is altering underneath, however i usually don't be concerned about this.
I believe you need to construct your navigation around just one SQL query which only modifies the LIMIT keyword. For instance Choose * FROM myTable LIMIT ,20 will return the very first 20 rows. Inside your PHP script for page 2 you'll then set the SQL query to become Choose * FROM myTable LIMIT 20,40 thus coming back rows from 20 to 40. Is that which you were searching for?
The navigation could be constructed with your PHP script simply by carrying out a choose COUNT(*) / rowsPerPage and therefore looping a structure of code before you satisfy the final amount of rows. The finish query would then LIMIT to lastLimit,TotalRowCount.
Edit: to compute the number of pages you have to compute all rows you should define the number of rows 1 page might have. You would do TotalRows/MaxPerPage = howManyPagesNeeded
2 inquiries to look into this context:
- how to deal with data that will get up-to-date/placed when you are viewing?
- the number of concurrent customers is going to be browsing the information, and can the applying have the ability to take care of the traffic if this brings data pages dynamically?
With respect to the solutions, a few of the other solutions here might apply.