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

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

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:

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!