bcamp1973 Posted February 23, 2007 Share Posted February 23, 2007 I'm trying to build pagination into list results of my web app. However, i'm not too sure how to handle the query. Obviously i need to know the total number of records in order to paginate, but i obviously don't need to capture all records per page. In other words, if i have 5000 records and i'm showing only 20 per page shouldn't i just query for the 20 i want to display? How do i do that and still get the count of the 5000 total? Link to comment https://forums.phpfreaks.com/topic/39836-efficient-queries-for-pagination/ Share on other sites More sharing options...
fenway Posted February 23, 2007 Share Posted February 23, 2007 Well, SQL_CALC_FOUND_ROWS can work for your first LIMIT 0,20 -- however, a LIMIT 4800,20 means you're throwing away 4800 records! If you're doing this a lot, and the order doesn't change, you may want to consider "ranking" them temporarily somewhere and then ordering based on WHERE clauses. Link to comment https://forums.phpfreaks.com/topic/39836-efficient-queries-for-pagination/#findComment-192431 Share on other sites More sharing options...
bcamp1973 Posted February 26, 2007 Author Share Posted February 26, 2007 Thanks fenway, that pretty much confirms what i was worried about. I might look into the temporary ranking solution. Is that easily implemented? I also found references to using cursors in my googling. I have zero experience with this and my ISP is running 4.1x so i'm not sure if it's even supported? Anyone have any thoughts on the following links? They're both for ASP (and I'm assuming SQL server) but maybe the concept could work in MySQL? Beeing a novice it's all over my head... http://aspadvice.com/blogs/jlovell/archive/2004/09/15/1992.aspx http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=50945 Link to comment https://forums.phpfreaks.com/topic/39836-efficient-queries-for-pagination/#findComment-194440 Share on other sites More sharing options...
fenway Posted February 26, 2007 Share Posted February 26, 2007 Well, you can rank with a temporary table that you keep updating periodically... as for cursors, that depends on your ODBC client. Link to comment https://forums.phpfreaks.com/topic/39836-efficient-queries-for-pagination/#findComment-194467 Share on other sites More sharing options...
Recommended Posts
Archived
This topic is now archived and is closed to further replies.