SitePoint Sponsor

User Tag List

Results 1 to 10 of 10
  1. #1
    SitePoint Zealot
    Join Date
    Nov 2006
    Posts
    127
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Paging with only next/previous

    I'm running PHP + MySQL (innodb) and in some pages, the paging is slowing down the system a lot.

    Now I've seen on some sites that have paging implemented in an interesting way. They only let you go forwards and backwards one page at a time, and some don't even tell you how many results there are.

    For instance if you look at google search results, they only says "about XXX results". Plus they never actually let you jump to the last page.

    I'm thinking of implementing my system this way in certain pages as well, to cut down on the load times. However, I'm exactly sure how that cuts down the load. Can someone shed some light on this?

  2. #2
    Follow Me On Twitter: @djg gold trophysilver trophybronze trophy Dan Grossman's Avatar
    Join Date
    Aug 2000
    Location
    Philadephia, PA
    Posts
    20,580
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    Determining the number of pages requires determining how many results there are for your query, which especially for InnoDB, may take significantly longer than stopping when the LIMIT is reached. Either not showing the number of results, or periodically updating a stored count that can be used as an estimate, would save you that time.

    Without knowing how many total results there are, you can't provide numbered paging, so the back/next method would be the only possible way to do so.

  3. #3
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,017
    Mentioned
    53 Post(s)
    Tagged
    2 Thread(s)
    you can say "... of approximately nnn results" and that wouldn't be a fib, either

    the ones that let you go only forward and backward one page at a time, they don't by any chance have urls like

    http://example.com/print.php?page=937

    do they?
    r937.com | rudy.ca | Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  4. #4
    SitePoint Zealot
    Join Date
    Nov 2006
    Posts
    127
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I've added dummy data into my database, and it appears that the more records I put in (around a couple of hundred thousand records now), the slower the query gets, even though I've removed the count.

    Now, the only other thing I'm doing is sorting by particular fields. Does this add much delay into the query? How can I make this faster?

    Quote Originally Posted by r937 View Post
    the ones that let you go only forward and backward one page at a time, they don't by any chance have urls like

    http://example.com/print.php?page=937

    do they?
    Would this impact performance?

  5. #5
    Follow Me On Twitter: @djg gold trophysilver trophybronze trophy Dan Grossman's Avatar
    Join Date
    Aug 2000
    Location
    Philadephia, PA
    Posts
    20,580
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    In order to sort, either an index or the entire table needs to be scanned to put rows in order before anything else can be done. The time that takes will be a function of the number of rows in the table. If you don't already have an index on the column you're sorting by, add one.

  6. #6
    SitePoint Zealot
    Join Date
    Nov 2006
    Posts
    127
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by Dan Grossman View Post
    In order to sort, either an index or the entire table needs to be scanned to put rows in order before anything else can be done. The time that takes will be a function of the number of rows in the table. If you don't already have an index on the column you're sorting by, add one.
    Yes there's already an index. Would it still slow down dramatically though, if the database is large?

    Right now there's about 200,000 records, and sorting results in the first page taking around .8 seconds and the last page around 1.5 seconds. Just doesn't sound right.

  7. #7
    Follow Me On Twitter: @djg gold trophysilver trophybronze trophy Dan Grossman's Avatar
    Join Date
    Aug 2000
    Location
    Philadephia, PA
    Posts
    20,580
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    You can always run an EXPLAIN query to see what's going on.

  8. #8
    SitePoint Zealot
    Join Date
    Nov 2006
    Posts
    127
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by Dan Grossman View Post
    You can always run an EXPLAIN query to see what's going on.
    This is my explain statement:
    Code:
    EXPLAIN SELECT u.id
    FROM users u
    WHERE u.status = 'Active'
    ORDER BY u.login_time DESC 
    LIMIT 166190 , 10;
    This is the output:
    Code:
    id = 1
    select_type = SIMPLE
    table = u
    type = index
    possible_keys = NULL
    key = login_time
    key_len = 8
    ref = NULL
    rows = 145687 
    Extra = Using where
    It seems the problem is probably in the number of rows fetched? Any way to cut that down? There's already an index in the "login_time" field, so that route's already taken. The query still takes around 2 seconds, and that's quite slow.

  9. #9
    Follow Me On Twitter: @djg gold trophysilver trophybronze trophy Dan Grossman's Avatar
    Join Date
    Aug 2000
    Location
    Philadephia, PA
    Posts
    20,580
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    A single index which contains the id, status, and login_time would be a covering index for the query and the table wouldn't need to be examined at all. If you actually select more than id's, then an index that contained the status and login_time columns would be optimal.

  10. #10
    SitePoint Zealot
    Join Date
    Nov 2006
    Posts
    127
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by Dan Grossman View Post
    A single index which contains the id, status, and login_time would be a covering index for the query and the table wouldn't need to be examined at all. If you actually select more than id's, then an index that contained the status and login_time columns would be optimal.
    Ok I've done that, as well as increase the pool buffer size:
    innodb_buffer_pool_size = 300M
    innodb_additional_mem_pool_size = 100M

    The server has only 512MB ram. It's a test server.

    Now, how do I test if the covering index is being used instead of a full table scan? I ran the explain again and it returned the same as before.


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
  •