# Is there a better way to page data than using LIMIT ? **URL:** \ **Category:** Other MySQL® Questions\ **Created:** [July 17, 2007, 1:18pm UTC](https://forums.percona.com/t/is-there-a-better-way-to-page-data-than-using-limit/387 "2007-07-17T13:18:57Z")\ **Posts on this page:** 4\ **Page:** 1
**Author:** ![richd](https://avatars.discourse-cdn.com/v4/letter/r/bc79bd/32.png) [@richd](https://forums.percona.com/u/richd)\ **Post date:** [July 17, 2007, 1:18pm UTC](https://forums.percona.com/t/is-there-a-better-way-to-page-data-than-using-limit/387/1 "2007-07-17T13:18:57Z")
Hi, The subject says it all really. I am curious if, on relatively large tables, there is a better way to get ‘paged’ data than using the LIMIT function. For example I have a comments table, it contains around 300k comments. The table structure is: comment\_id INT item\_ref INT author\_ref INT date\_created DATE\_TIME status ENUM(‘pending’,‘live’,‘deleted’) The actual comment text is held in comment\_body: comment\_ref INT message TINYTEXT And finally I’ve a comment\_stats table: comment\_ref INT total\_views INT I split the ‘total views’ out into a separate table because this table receives a large number of update requests, where-as the main comment and comment\_body tables are heavily read, but rarely modified. Keeping the comment text itself out of the main comment table kept the table fixed. comment\_body is LEFT JOINed on the comment\_id to comment\_ref. comment\_id is my primary key (auto inc, unsigned) In order to page through the comments, which are displayed based on date\_created, I’m using LIMIT X,Y in order to show 50 comments per page. The ‘item\_ref’ field is the link to the item being commented on, so for example some items may only have a couple of comments, while others will have thousands. I program with PHP 5 (PDO extension) and I am having to run my query once with a COUNT(\*) at the start in order to get back table totals, and then again with LIMIT at the end to get the block of rows that I want to display. Using explain shows that it is checking ALL comments matching item\_ref, even though it only returns the 50 I require. Is there a better way to do this? I’m perfectly happy to move my query into a stored procedure if it will help (and use a cursor to loop through the data?) but is the problem more intrinsic than that? Perhaps the solution isn’t MySQL based at all, maybe I should just be looking at caching say the first few pages worth of results for each item and not worry about the MySQL hit? Eitherway I’d truly appreciate your advice. ---
**Author:** ![richd](https://avatars.discourse-cdn.com/v4/letter/r/bc79bd/32.png) [@richd](https://forums.percona.com/u/richd)\ **Post date:** [July 17, 2007, 1:27pm UTC](https://forums.percona.com/t/is-there-a-better-way-to-page-data-than-using-limit/387/2 "2007-07-17T13:27:27Z")
Sorry, forgot to add we’re using MySQL 5.0.27 ---
**Author:** ![myrddin](https://avatars.discourse-cdn.com/v4/letter/m/6a8cbe/32.png) [@myrddin](https://forums.percona.com/u/myrddin)\ **Post date:** [July 20, 2007, 10:24am UTC](https://forums.percona.com/t/is-there-a-better-way-to-page-data-than-using-limit/387/3 "2007-07-20T10:24:03Z")
You can try using SQL\_CALC\_FOUND\_ROWS However you have to test it and see what performance you get and also be aware of :- [URL][MySQL Bugs: #18454: SQL\_CALC\_FOUND\_ROWS should calculate rows during tmp table builds/filesorts](http://bugs.mysql.com/bug.php?id=18454%5B/URL%5D) ---
**Author:** ![carpii](https://avatars.discourse-cdn.com/v4/letter/c/b5ac83/32.png) [@carpii](https://forums.percona.com/u/carpii)\ **Post date:** [July 22, 2007, 11:24am UTC](https://forums.percona.com/t/is-there-a-better-way-to-page-data-than-using-limit/387/4 "2007-07-22T11:24:54Z")
consider denormalising a little bit You do an expensive count, just to see how many pages there are. The paging isnt a performance problem, but the count potentially is. But if instead, you were to store num\_comments in the items table, you could just figure out how many pages there are based on that, and jump straight to the query with a LIMIT