MySQL fast query to get latest five records

Asked Jul 28 '22 at 06:46

Active Jul 30 '22 at 01:43

Viewed 36 times

i have this query to get 5 latest post of each user. it works fine on small amount of data. but on live where i have millions of data. it's not very fast. it's taking too long. if anyone have idea to optimize the query so it's fast on large amount of data.

select p.*
from posts p
where p.id  >= (select p2.id
                      from posts p2
                      where p2.user_id  = p.user_id
                      order by p2.id desc
                      limit 1 offset 4
                     )
                     order by p.user_id ;

edited Jul 30 '22 at 01:43

Rick James

135,179
13
127
222

asked Jul 28 '22 at 06:46

Rizwan Shahzada

Use `partition over (select ...)`. – Wiimm Jul 28 '22 at 06:48
What is the goal of such a query without limit on the users? if each user has only 5 posts, the query is supposed to return millions/5 of posts ? – N69S Jul 28 '22 at 06:50
@Wiimm can you please give me a example ? – Rizwan Shahzada Jul 28 '22 at 06:52
What is precise MySQL version? – Akina Jul 28 '22 at 06:53
MySQL version is 5.7 – Rizwan Shahzada Jul 28 '22 at 06:53
no there is thousands of users and millions of posts, so i want to get only five post of each user. @N69S – Rizwan Shahzada Jul 28 '22 at 06:55
@RizwanShahzada so millions - 4 x thousands of results, so still millions of results in a single query. how fast do you expect it to be when each result has to run a subquery to check for that condition? – N69S Jul 28 '22 at 07:00
Sorry, but I wrote a wrong syntax in my first fast comment. Read https://mariadb.com/resources/blog/introduction-to-window-functions-in-mariadb-server-10-2/ section "Top-N Queries" for details. Or search for `mysql window functions`. – Wiimm Jul 28 '22 at 07:38
https://stackoverflow.com/questions/12113699/get-top-n-records-for-each-group-of-grouped-results – juergen d Jul 28 '22 at 08:20
That query looks "wrong". The outer part says `p.id >= ...`, which will include posts from a variety of `user_ids`. See the tag I added. – Rick James Jul 30 '22 at 01:46
Alas, the "partition over" technique makes two read passes over the table and one write pass. – Rick James Jul 30 '22 at 02:37
With, say, 1000 users, you really want 5000 rows in the resultset? – Rick James Jul 30 '22 at 02:39

MySQL fast query to get latest five records

0 Answers0