display all results vs spliting up over number of pages

[mickthetree]

Hi All

I have built a site for a local auction house. Each auction has around 2000 lots, which are held in the table auction_items with references to another 3 tables.

I am trying to work out the best way of displaying all of the items for a given auction, whilst optimising performance.

Each site visitor typically wants to see all of the items in the list and wants the option to "show all" items in a single view.

From past hits we estimate approximately 700 hits a day, most of which will want to view the list of items.

Are there performance benefits to be gained from displaying the results 20 to a page and having the users scroll through? Rather than loading the full list once per user?

From a usability point of view they would be better off with one list that they can see "at a glance" and print.

I look forward to your comments and suggestions.

[adwatson]

Seems like showing a list of 2000 items will make for an awful long page, so my instinct would be to do paging. But I suppose it'd be easy enough to allow it at first and see what the reaction from users is, as well as the load on the server. Then try adding a paging effect using LIMIT on the queries and see if that's better.

[longneck]

i'm a big fan of paging, especially if the user can control the number of items per page.

[r937]

Are there performance benefits to be gained from displaying the results 20 to a page and having the users scroll through?

the answer is a qualified "probably not"

on the database side, when you use ORDER BY summat LIMIT 60,20, the database still retrieves all rows, but only returns 20

presumably most of the cpu cycles consumed are to retrieve the result set, and then the amount of data flowing up the pipe from the database engine to your front-end language is minimal

on the other hand, loading the full list once per user at least has the potential of query caching, which means second and subsequent pages require no database call at all -- although i am not the right guy to help you tweak and tune this