#1
  1. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Mar 2001
    Location
    Iceland
    Posts
    35
    Rep Power
    19

    Question Which method is better?


    I have a small/medium news site with a paged result set that is executed in this manner

    Current Solution:
    Select all rows, count them to find out how many pages we need, find which of the rows to display and display them.

    This is done on every page and to me it sound a like a waste of system resources to select all rows on every page.

    Luckily I think I have a better way but I'm no sql guru and don't know any benchmark programs, ok on to the solution.

    Hopefully a better solution:
    Find out what rows to display and select only those rows, then in a second sql statment select count statment to find how many pages we need

    I't sounds better to me but what do you experts think?
    P.S. can you point out to me an easy sql benchmarking program

    Regards
    Andri Mar Jónnson
  2. #2
  3. mod_dev_shed
    Devshed Supreme Being (6500+ posts)

    Join Date
    Sep 2002
    Location
    Atlanta, GA
    Posts
    14,800
    Rep Power
    1104
    That does sound better. No need to get all that information just so you can count the results -- just count(*) them.
    Code:
    SELECT count(*) FROM table
    
    # then
    
    SELECT * FROM table LIMIT $offset, $per_page
    # Jeremy

    Explain your problem instead of asking how to do what you decided was the solution.

IMN logo majestic logo threadwatch logo seochat tools logo