SitePoint Sponsor

User Tag List

Results 1 to 4 of 4
  1. #1
    SitePoint Enthusiast
    Join Date
    Mar 2005
    Posts
    98
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    display all results vs spliting up over number of pages

    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.

  2. #2
    SitePoint Addict
    Join Date
    Apr 2006
    Posts
    249
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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.

  3. #3
    reads the ********* Crier silver trophybronze trophy longneck's Avatar
    Join Date
    Feb 2004
    Location
    Tampa, FL (US)
    Posts
    9,854
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    i'm a big fan of paging, especially if the user can control the number of items per page.
    Check out our new Industry News forum!
    Keep up-to-date with the latest SP news in the Community Crier

    I edit the SitePoint Podcast

  4. #4
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,347
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by mickthetree View Post
    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
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •