Most efficient way to get oldest row in MySQL by id

I have 2 tables:

conversation

CREATE TABLE `conversation` (
  `conversation_id` int(11) NOT NULL,
  `title` varchar(200) COLLATE utf16_czech_ci NOT NULL,
  `beginning_date` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `user_id` int(11) DEFAULT NULL
)

message

CREATE TABLE `message` (
  `message_id` int(11) NOT NULL,
  `text` varchar(5000) COLLATE utf16_czech_ci NOT NULL,
  `add_date` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `is_seen` int(11) NOT NULL,
  `conversation_id` int(11) NOT NULL,
  `user_id` int(11) DEFAULT NULL
)

When I create a new conversation, I also need to create a new message (the beginning message for the conversation). Later, this conversation may have thousands of messages. But what I need to do in certain situations is that I need to take a conversation with certain conversation_id from database and get the oldest message (the beginning message for the conversation) for this conversation. What is the most efficient way to get this message?

I did something like this but the ordering may slow it later, when conversations have maaany messages:

SELECT * FROM message WHERE conversation_id=some_id ORDER BY add_date ASC LIMIT 1

For the specific query that gets only one row from the table:

SELECT *
FROM message
WHERE conversation_id = @some_id
ORDER BY add_date ASC
LIMIT 1 ;

you only need a common (btree) index on (conversation_id, add_date). The query will need to do only an index seek (which will find the PK value) and then a seek on the table. The operations should be both very fast (O(logn)), so the size of the table will not matter much.

After you add the index (or any index), you don't need to do anything more. Whenever a query runs, if the optimizer decides that the index is useful (for that query, which can be the specific query above or any other), then it will use the index.

Side notes: please add PRIMARY KEYs to the tables.

StackExchange Database Administrators Q#172383, answer score: 7