How to SELECT most common (viral) from a table and LIMIT results for pagination? 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!
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.