I have what seems like a simple query, and my indexes seem to be sufficient without the INNER JOIN, but after I add the join, I end up seeing "Using Temporary". Would also appreciate any good resources for learning more about query optimization!
SELECT SQL_CALC_FOUND_ROWS DISTINCT m.id FROM messages m INNER JOIN message_entries t2 ON m.id = t2.message_id WHERE m.user_id = 1234 AND m.type = 1 ORDER BY m.sent_on DESC;
The messages table has a covering index for (user_id, type, sent_on, created) and it uses this index for the query, and the message_entries table has an index on message_id (through foreign key).
I've tried adding id to the covering index on the messages table (at the beginning or end of the index, i've tried both), but it still chooses to use the original index for user_id, type, sent_on, created.
What can I do to improve performance when adding joins to the base query?