Re: I need help in optimzing a order by clause in a join query

[Rick James]

How does innodb_buffer_pool_size compare to the size of both tables?

Try

SELECT  m2.*
    FROM  messages m2
    JOIN  
      ( SELECT  message_id
            FROM  messages AS m1
            JOIN subscription AS s  ON s.entity_id = m1.entity_id
            WHERE  s.user_id = 1
            ORDER BY  m1.timestamp DESC
            LIMIT  50
      ) x ON m.message_id = x.message_id;