Efficient comment system pagination query

Question

So I've been looking around the web about any information about pagination. From what I've seen there are 3 kinds, (LIMIT, OFFSET) a, (WHERE id > :num ORDER BY id LIMIT 10) b and (cursor pagination) c like those used on facebook and twitter.

I decided that for my project I'll go with the "b" option as it looks pretty straightforward and efficient.

I'm trying to create some kind of "facebook" like post and comment system, but not as complex.

I have a ranking system for the posts and comments and top 2 comments for each post that are fetched with the post.

The rest of the comments for each specific post are being fetched when people click on to see more comments.

This is a query for post comments:

SELECT 
  c.commentID,
  c.externalPostID, 
  c.numOfLikes, 
  c.createdAt, 
  c.customerID, 
  c.numOfComments,
  (CASE WHEN cl.customerID IS NULL THEN false ELSE true END) isLiked,
  cc.text,
  cu.reputation,
  cu.firstName,
  cu.lastName,
  c.ranking
  FROM 
    (SELECT * 
     FROM Comments 
     WHERE Comments.externalPostID = :externalPostID) c
    LEFT JOIN CommentLikes cl ON cl.commentID = c.commentID AND cl.customerID = :customerID
    INNER JOIN CommentContent cc ON cc.commentTextID = c.commentID
    INNER JOIN Customers cu ON cu.customerID = c.customerID 
  ORDER BY c.weight DESC, c.createdAt ASC LIMIT 10 OFFSET 2

offset 2 is because there were 2 comments being fetched earlier as top 2 comments.

I'm looking for a way similar to this of seeking next 10 comments each time through the DB without going through all the rows like with LIMIT,OFFSET

The problem is that I have two columns that are sorting the results and I won't allow me to use this method:

SELECT * FROM Comments WHERE id > :lastId LIMIT :limit;

HUGE thanks for the helpers !

Solution So Far:

In order to to have an efficient pagination we need to have a single column with as much as possible unique values that make a sequence to help us sort the data and paginate through.

My example uses two columns to sort the data so it makes a problem. What I did is combine time(asc sorting order) and weight of the comment(desc sorting order), weight is a total of how much that comment is being engaged by users.

I achieved it by getting the pure int number out of the DateTime format and dividing the number by the weight let's call the result,"ranking" . this way a comment with a weight will always have a lower ranking ,than a comment without a weight.

DateTime after stripping is a 14 digit int ,so it shouldn't make a problem dividing it by another number.

So now we have one column that sorts the comments in a way that comments with engagement will be at the top and after that will come the older comments ,so on until the newly posted comments at the end.

Now we can use this high performance pagination method that scales well:

SELECT * FROM Comments WHERE ranking > :lastRanking ORDER BY ASC LIMIT :limit;

Im looking to implement a pagination practice like is shown here developer.wordpress.com/2014/02/14/… — Ariel Estrin
But you seem to have a column holding the ranking already, so I do not understand your question! You are ordering by 2 columns, so you need to note the values of the last displayed record and use thise. — Shadow
Ranking is not a unique value column lets say several comments can have ranking of 0 or 10. For that to work from what i understand i need a column with a unique identifier that will have some sort of sequence when ordering by this identifier. For example a shop with products can order by product id and in the where clause look for productID that is greater than the previous one fetched. — Ariel Estrin

Terlan Abdullayev Terlan Abdullayev · Accepted Answer · 2018-02-02T13:45:08

Ok i want to say about other way, in my opinion this very useful.

$rowCount = 10; //this is count of row that is fetched every time
$page = 1; //this is for calculating offset .  you must increase only this value every time    
$offset = ($page - 1) * $rowCount; //offset

SELECT 
  c.commentID,
  c.externalPostID, 
  c.numOfLikes, 
  c.createdAt, 
  c.customerID, 
  c.numOfComments,
  (CASE WHEN cl.customerID IS NULL THEN false ELSE true END) isLiked,
  cc.text,
  cu.reputation,
  cu.firstName,
  cu.lastName,
  c.ranking
  FROM 
    (SELECT * 
     FROM Comments 
     WHERE Comments.externalPostID = :externalPostID) c
    LEFT JOIN CommentLikes cl ON cl.commentID = c.commentID AND cl.customerID = :customerID
    INNER JOIN CommentContent cc ON cc.commentTextID = c.commentID
    INNER JOIN Customers cu ON cu.customerID = c.customerID 
  ORDER BY c.ranking DESC, c.createdAt ASC LIMIT $rowCount OFFSET $offset

There can be an error because i didn't check it , please don't make it matter

Efficient comment system pagination query

1 Answers