0

I have an sql table for some chats with a table messages, containing id int pk, chat_id int, created_at timestamp.

Given a message id, is it possible to select it, along with 5 messages after it in same chat, in single query?

Or can I somehow combine the two following subqueries (having starting_message_id as an input)?

SELECT * FROM messages WHERE chat_id IN (
    SELECT chat_id from messages where id='starting_message_id'
) AND created_at > (
    SELECT created_at from messages where id='starting_message_id'
) ORDER BY created_at ASC LIMIT 5;

I feel that only two requests are sufficient:

  1. selecting chat_id and created_at columns by chat id
  2. selecting some messages having all data known

What would the correct optimised syntax be?

1
  • In future, could you please provide your table structures as DDL (CREATE TABLE foo (...);) and your data as DML (INSERT INTO foo VALUES (...);). p.s. welcome to the forum! p.p.s you should upgrade to MySQL 8.xx (now at 8.19 AFAIK) - you will then have the ROW_NUMBER() function which would make your query trivial!
    – Vérace
    Commented Mar 17, 2020 at 10:04

1 Answer 1

1

I believe you want something like:

SELECT msg.* 
FROM messages msg
JOIN (SELECT chat_id, created_at
  FROM messages
  where id='starting_message_id') nested
ON (msg.chat_id=nested.chat_id and msg.created_at>=nested.created_at)
ORDER BY msg.created_at
LIMIT 6;
2
  • Looks good, but mystic.. I heard, that JOIN joins two tables together. But seems there are many types of them. Could you explain, what is this type and does it works here? Is it more effective than 2 separate requests? 3 requests without join? Commented Mar 17, 2020 at 10:22
  • This type is inner join. Yes, it works. I don't know about 2 and 3 separate requests, but if you have an index on created_at, I expect that the difference in execution time between this query and the query you posted in the question will be insignificant. You can always test it empirically.
    – dbilid
    Commented Mar 17, 2020 at 10:36

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.