Most recent related entries from another table

Question

I've got a table of users (1,000s) and a table of user messages (100,000s). I want a fast way of getting all users and their most recent message.

What I'm currently using is something like...

SELECT 
   u.id, u.name, 
   (
      SELECT note FROM msgs 
      WHERE msgs.uID=u.id 
      ORDER BY created_date DESC
      LIMIT 1
   ) as note
FROM users u

Right now if I limit that to 20 users, it takes 2.5s ... 200 users takes 45s.

(I already have an INDEX on msgs.uID and msgs.created_date.)

What am I doing wrong? I need a much faster query.

score 1 · Answer 1 · edited May 23 '17 at 10:26

1

I searched before posting (with no luck), but found this solution in the "related" sidebar just after posting.

SELECT    u.id, u.first_name, msgs.note
FROM      users u
JOIN      (
              SELECT    MAX(created_date) max_date, user_id 
              FROM      msgs 
              GROUP BY  user_id
          ) msgs_max ON (msgs_max.user_id = u.id)
JOIN      msgs m ON (msgs.created_date = msgs_max.max_date AND u.id = msgs.user_id)

Considerably better, but still ~1.3s on my tables. Can't MySQL do this much faster?

edited May 23 '17 at 10:26

Community

1
1

answered Feb 05 '15 at 21:54

Timothy Aaron

3,059
19
22

We can't help further without know a) the structure of your tables and b) the output of the EXPLAIN for the above – Strawberry Feb 06 '15 at 00:36

score 0 · Answer 2 · answered Feb 05 '15 at 21:44

0

select users.*, msgs2.* from users inner join ( select msgs.* from msgs inner join ( select max(created_date) as dt, user_id from msgs group by user_id ) last_dt on msgs.user_id = last_dt.user_id and msgs.created_date = last_dt.dt ) msgs2 on users.id = msgs2.user_id

Try this, I am sorry if this may be has some syntax error, or may be also works slow, I write this query directly, without any test.

Just try.

answered Feb 05 '15 at 21:44

Oto Shavadze

40,603
55
152
236

Much better, but still coming up ~2.8s for me. – Timothy Aaron Feb 05 '15 at 21:55
Why the sub select for the outer instance of msgs instead of a simple join to the users table? – symcbean Feb 05 '15 at 22:08

score 0 · Answer 3 · edited May 23 '17 at 11:49

0

Solution with just two joins

SELECT u.id,u.name,msgs_max.note FROM users u JOIN
(SELECT m1.uID, m1.note FROM msgs m1 LEFT JOIN msgs m2
ON (m1.created_date < m2.created_date 
AND m1.uID = m2.uID)
WHERE m2.id IS NULL) msgs_max
ON u.uID=msg_max.uID

Reference: https://stackoverflow.com/a/123481/2180715

edited May 23 '17 at 11:49

Community

1
1

answered Feb 06 '15 at 00:26

user2180715

1
1

Most recent related entries from another table

3 Answers3