I work on a simple chat application with database. The database for the project is shown in the image.
The direction column in the message table is just to determine if user1 is the sender or user2 is the sender.
I use this query to get the messages :
SELECT TextContent,Direction FROM Messages
WHERE User1 = 1 AND User2 = 2
ORDER BY Date
The values for User1 and User2 is just for clarification.
I have two questions :
1. Should I create an index on (User1,User2,
Date) columns or on (User1,User2,ID) columns ?. Should I create a cluster index or non-cluster index ?
2. I didn't choose a primary key for Messages table. Should I simply choose the ID column as a primary key or select multiple columns for the primary key ?
Go to the complete details ...