How to choose indexes for the chat application database
I am working on a small chat application with a database. I am creating a database shown in the image.
I am new to indexes and I want to select the appropriate indexes for a query.
Can I use a clustered index on the Messages table? and if I can, which column (or columns) should the clustered index be on? or should I use a non-clustered index?
Refresh . The request I'm getting from the user:
Select TextContent From Messages where (SenderId='1' and ReciverID = '2') or (SenderId='2' and ReciverID = '1') order by date
The values for SenderID and ReciverID are for clarification.
source to share
You should probably add the surrogate primary key to the messages table and create an index:
ALTER TABLE messages ADD COLUMN id BIGINT NOT NULL IDENTITY
ALTER TABLE messages ADD CONSTRAINT pk_messages_id PRIMARY KEY (id)
CREATE INDEX ix_messages_sender_receiver_date (senderId, receiverId, date) ON messages
If you only want to get, say, only the last 10 messages from the conversation thread, this might help rewrite your query a bit:
SELECT m.*
FROM (
SELECT TOP 10
*
FROM (
SELECT date, id
FROM messages
WHERE senderId = 1 AND receiverId = 2
UNION
SELECT date, id
FROM messages
WHERE senderId = 2 AND receiverId = 1
) q
ORDER BY
date DESC, id DESC
) q
JOIN messages m
ON m.id = q.id
Thus, SQL Server is more likely to merge, join the two streams of conversation, rather than sort them separately.
Alternatively, instead of sender and receiver, use user1
, user2
and direction
to user1 < user2
(always) and direction
determine whether the text goes from user1
to user2
or vice versa.
This way you can always just filter only user1 = 1 AND user2 = 2
without worrying about OR
or joins.
You can do this on computed columns, which you can index as well:
ALTER TABLE messages ADD COLUMN user1 AS CASE WHEN senderId < receiverId THEN senderId ELSE receiverId END
ALTER TABLE messages ADD COLUMN user2 AS CASE WHEN senderId > receiverId THEN senderId ELSE receiverId END
ALTER TABLE messages ADD COLUMN direction AS CASE WHEN senderId < receiverId THEN 0 ELSE 1 END
CREATE INDEX ix_messages_user1_user2_date ON messages (user1, user2, date)
then select:
SELECT *
FROM messages
WHERE user1 = 1
AND user2 = 2 -- make sure lower number goes to user1, higher number goes to user2
ORDER BY
date
source to share