0

i have a table similar to below, which has GUID as the key. i am trying to display the content of tis using paging which has GUID as key, but running into issue of how do i do that?

CREATE TABLE `planetgeni`.`PostComment` (
  `PostCommentId` CHAR(36) DEFAULT NULL,
  `UserId` INT   NOT NULL,
  `CreatedAt` DATETIME NULL DEFAULT NULL ,
  .
  .
  .
   PRIMARY KEY (`PostCommentId`)
    )
 ENGINE=InnoDB DEFAULT CHARSET=latin1;

if it was a Int key my Stored procedure would look something like this , giving me next 10 order by desc. But with GUID not sure how to do that type of paging.

getPostComment( int lastPostID)
   where PostCommentId< lastPostID order by PostCommentId desc LIMIT 10;
Justin Homes
  • 3,739
  • 9
  • 49
  • 78
  • Do you have a post date/time field in that table? I would order by that. – James Jun 07 '14 at 03:18
  • @James i do have datetime field, but if we have more than one row with same date time we can skip that during where createdAT> lastdate order by createdATand limit n. – Justin Homes Jun 07 '14 at 12:46
  • Even if you'd get this working, would your users not mind getting random next 10 posts? I'd just add an indexed auto-increasing numeric column to go with the GUID id --> you get the posts in order and you can use the normal method for paging. – Esko Piirainen Jun 17 '14 at 12:21

2 Answers2

0

You can still do this with GUID's, but since GUID's are pseudorandom, when you ORDER BY postcommentid the order probably won't be what you want. You probably want something in approximately chronological order, and as you sort by the random GUID, the order will be repeatable, but random.

As @James comments, you could use another column for the sort order, but that column would need to be unique, or else you would either miss some duplicate rows (if you use >) or repeat values on the next page (if you use >=).

You'll just have to use LIMIT with OFFSET. MySQL optimizes LIMIT queries, so it quits examining rows once it finds the rows it needs for the page. But it also must examine all the preceding rows, so the query gets more expensive as you advance through higher-numbered pages.

A couple of ways to mitigate this:

  • Don't let your users view higher-numbered pages. Definitely don't give them a direct link to the "Last" page. Just give them a link to the "Next" page and hope they give up searching before they advance so far that the queries become very costly.

  • Fetch more than one page at a time, and cache it. For instance, instead of LIMIT 10, you could LIMIT 70 and then keep the results in memcached or something. Use application code to present 10 rows at a time, until the user advances through that set of rows. Then only if they go on to the 8th page, run another SQL query. Users typically don't search through more than a few pages, so the chance you'll have to run a second or a third query become very small.

Bill Karwin
  • 538,548
  • 86
  • 673
  • 828
0

Change column by which You are using in 'order by'. 

getPostComment( int lastPostID)
    where PostCommentId< lastPostID order by CreatedAt,UserId desc LIMIT 10;
Andrzej Reduta
  • 767
  • 1
  • 7
  • 15