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

    Join Date
    Jun 2012
    Posts
    12
    Rep Power
    0

    Scrolling/Paging through large database


    Hello:
    My client has a very large database (5.3 million records). These records are indexed by ownername, which can be duplicates. They want to be able to display 25 records at a time with the ability to display the next 25 or previous 25 and scroll through the table. I have tried SELECT * FROM table ORDER BY ownername LIMIT 0,25, increasing/decreasing the offset by 25 each time they click a previous/next button. Works great at 1st but as the offset increases the query takes forever. ie LIMIT 1000000,25.
    Can anyone assist on a way to allow paging what works efficiently?
    Thanks in advance
  2. #2
  3. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jun 2012
    Posts
    12
    Rep Power
    0

    Paging through large database


    Hello:

    My client has a very large database (5.3 million records). These records are indexed by ownername, which can be duplicates. They want to be able to display 25 records at a time with the ability to display the next 25 or previous 25 and scroll through the table. I have tried SELECT * FROM table ORDER BY ownername LIMIT 0,25, increasing/decreasing the offset by 25 each time they click a previous/next button. Works great at 1st but as the offset increases the query takes forever. ie LIMIT 1000000,25.
    Can anyone assist on a way to allow paging what works efficiently?

    Thanks in advance
  4. #3
  5. No Profile Picture
    Problem Solver
    Devshed Specialist (4000 - 4499 posts)

    Join Date
    Jan 2001
    Location
    Stockholm, Sweden
    Posts
    4,495
    Rep Power
    534
    This is a classic problem.

    The ORDER BY humpty LIMIT x,y internally needs the data in sorted order and needs to scan through all x records before it can return the y amount of them.

    So if x starts to become large you will pay a penalty.

    Usually you can circumvent it by in some way limit the amount of rows with a WHERE clause. Of course you can have a hard time to know what that WHERE is if you have duplicates etc etc. Best of all is numeric primary key like an INT. It makes the index size small, you have a fairly predictable amount of values (although you can have "holes" in the sequence in the form of deleted rows), etc.

    But in your case you could perhaps get the customer to first choose the first letter in the ownername before they start to page through the data. That way you have split the load to work with by 26 or maybe the first two letters and split it by 676.

    You could even handle it in some elaborate way in your own code.

    But the point is that a query like:
    Code:
    SELECT
      ...
    FROM
      humpty
    WHERE
      ownername LIKE 'a%'
    ORDER BY
      ownerename
    LIMIT x,y
    with a sensible WHERE on an indexed column is much faster than one without the where where all rows needs to be in sorted order and scanned.

    Good luck!
    /Stefan

IMN logo majestic logo threadwatch logo seochat tools logo