Choosing good indexes for my queries

Question

I have a table which looks like this:

Messages table:

Message_ID (BIGINT) PK auto_increment
FromUser (INT 16)
ToUser (INT 16)
DateCreated (Timestamp)
MessageText (Varchar (500) )
HasRead (TINYINT (0=false, 1=true) )

The queries which will be run will include:

SELECT * 
FROM messages 
WHERE FromUser = '10000000' 
ORDER BY DateCreated DESC 
LIMIT 10;

SELECT * 
FROM messages 
WHERE ToUser = '10000000' 
ORDER BY DateCreated DESC 
LIMIT 10 ;

UPDATE messages 
SET HasRead = 1 
WHERE Message_ID = '123456789'; // When a message has been read update it to show it has been read.

As a result I have created the following indexes:

• Message_ID (primary index)
• (FromUser,DateCreated) BTREE DESC
• (ToUser,DateCreated) BTREE DESC

I have the following conclusions:

• The MessageID primary index is used for the update operation.
• The composite FromUser andDataCreated index are is used to get all the messages from a user. My question here is if this is a proper index? Can I just include FromUser or do I need both if I am also sorting (User can have a lot of messages)? If I drop DateCreated using explain I can still see it uses this index, but then it seems to use filesort to sort; is this efficient or is it better to have a larger index and use the index to get the sorted values?
• The composite ToUser and DataCreated index are is used to get all the messages a user sent. My question here again is if this is a proper index? Can I just includeToUser or do I need both if I am also sorting (User can have a lot of messages)? If I drop DateCreated using explain I can still see it uses this index, but then it seems to use filesort to sort; is this efficient or is it better to have a larger index and use the index to get the sorted values?

These are chat messages so they will accumulate quickly. Would it be wise to partition as well? If so would what would be a good partition strategy? If I partition a range on from users it will work great as a users messages will be stored on the same partition but to users won't be on the same partition. Vice versa also, so I can't find a good partition key

Answer 1

The questions:

• Your indexes are appropriate for these queries and your conclusions are correct.

• About partitions, they are rarely useful for performance. I don't think there is any point in using them in your case.

Minor remarks:

• The DESC doesn't mean anything in that place. You can (try to) define a composite index with 4 ways:

  (FromUser ASC, DateCreated ASC)
  (FromUser ASC, DateCreated DESC)
  (FromUser DESC, DateCreated ASC)
  (FromUser DESC, DateCreated DESC)
  

  but MySQL does not supoort DESC indexes and will silently ignore these ASC and DESC. The index created will be the (ASC, ASC) one.

  Nothing to worry though as the indexes can and will be used for the queries you have. Indexes can be read fowards or backwards. The index would not be very efficient if you had (for example) a query with:

  ORDER BY FromUser ASC, DateCreated DESC
  

  where an (ASC, DESC) index would be better.