ASP.NET GridView Pagination using MySQL LIMIT

Question

I have a GridView which I bind at runtime to a dataset via a query. I handle the pagination using the PageIndexChanging and PageIndexChanged events to set the GridView's PageIndex property on postback from the pager. While this functions correctly, the problem is the performance with >7800 rows in this table. It has to return the entire result set for every paging operation.

If I use the MySQL LIMIT offset,pagesize clause, I get results in <50ms. My question is how can I use the LIMIT clause in my query to speed up pagination? I somehow need to calculate the offset dynamically. I know the page size at runtime via GridView.PageSize.

UPDATE: My new problem is that the GridView pagination controls disappeared, presumably because only 10 records return from each query (PageSize=10)

Thanks, Mark

Answer 1

You also know the page number, right? Offset by the pagenumber * pagesize.