3

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

6

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.

1

It appear that performance here is of utmost importance and message order is also of utmost importance i.e. message order will be frequently use and it will be use for so many purposes.

you can keep one extra column in message table called "MessageOrder" int ,either autoincreament or user define increaament.

In nutshell messageorder is more like row_number than autoincreament id.

OR you can treat message_id in same manner.

Then your message_ID can be CI. Conversation_ID as NCI .

and your query will be like,

SELECT *
FROM message
WHERE conversation_id=some_id 
  AND message_id=1

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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