How to SELECT most common (viral) from a table and LIMIT results for pagination?

Question

Sorry if it's a dubplicate but I can't find an answer to the question.

Hello there so I have three tables users and articles and likes, a user can write an article, in the table articles there is also a timestamp column and can be liked by other users where a like is saved in likes table with the user id and secret id.

articles table:

+----+---------+--------------------+
| id | article |       timestamp    |
+----+---------+--------------------+
| 1  | A       |2019-02-10 22:19:02 |
| 2  | B       |2019-02-9 22:20:28  |
| 3  | C       |2019-02-9 22:21:10  |
| 4  | D       |2019-02-8 18:20:10  |   
| 5  | E       |2019-02-8 13:25:04  |
+----+---------+--------------------+

likes table:

+-----------+--------+
| articleid | userid |
+-----------+--------+
| 1         | 1      |
| 1         | 3      |
| 1         | 5      |
| 1         | 3      |
| 2         | 1      |
| 3         | 2      |
| 3         | 3      |
| 3         | 4      |
| 3         | 5      |
| 3         | 2      |
| 4         | 4      |
+-----------+--------+

I'm trying to do the pagination of the most liked articles in the shortest time,

as for example in this case article A is the top of the order because it got more likes in less time,

How would I do that and at the same time LIMIT the result to do the pagination?
My try was:

SELECT * FROM articles, (SELECT count(*) FROM likes WHERE articleid = id) as likes
GROUP BY articles
OREDER BY likes
LIMIT 10

I would do it in php without LIMIT but at the same time I want to repeat the query to get the next articles for the second page in the same way and I have no idea how to do that.
Thanks in advance!

Answer 1

Once the database is more than a 'toy', you will need to keep an up-to-date (or nearly so) table of actual like-counts. It would have two columns: article_id and like_ct with article_id as the PK.

With that (plus a JOIN) you can do an efficient pagination without OFFSET.

most liked in the last day or two days

Start with a 2-column table:

CREATE TABLE Likes (
    article_id ...,   -- for JOINing to Articles
    ts TIMESTAMP NOT NULL,
    PRIMARY KEY (article_id)
) ENGINE=InnoDB;

This should give you the list of article_ids ordered by most likes in the last 2 days, counting back from the instant the query was run.

SELECT article_id,
       COUNT(*) AS like_ct
    FROM Likes
    WHERE ts >= NOW() - INTERVAL 2 DAY
    GROUP BY article_id
    ORDER BY tot_likes DESC

With LIMIT 10 you can get the top 10. With OFFSET you can inefficiently get other 'pages'.

Use the above as a "derived" table and JOIN Articles USING(article_id) to get other info.

Answer 2

ORDER BY likes DESC should be used to get the highest likes first.

The second page is going to have LIMIT 10,10.

With a likes table as it is, its going to be hard to get quick performance out of this. Getting the number of likes into the main articles table would help get a quicker results of this if they are used often. If you need to use both perhaps an INSERT trigger on the likes table.

Once you have a likes column in articles, it will need an index on it.

Then your query becomes simply:

SELECT *
FROM articles
ORDER BY likes DESC
LIMIT 10

The second page way is:

SELECT *
FROM articles
ORDER BY likes DESC
LIMIT 10,10

If you got too many pages, you'll eventually start to be slow down as getting 10 pages at an offset needs to walk down to that offset first.