chat messager mysql optimizations
I have a chat system and I want some ideas to optimize this:
CREATE TABLE IF NOT EXISTS `chat` (
`id` int(11) UNSIGNED NOT NULL AUTO_INCREMENT,
`from` int(11) UNSIGNED NOT NULL,
`to` int(11) UNSIGNED NOT NULL,
`message` text NOT NULL,
`sent` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
`recd` int(11) UNSIGNED NOT NULL DEFAULT '0',
`del_from_status` int(11) NOT NULL DEFAULT '0',
`del_to_status` int(11) NOT NULL DEFAULT '0',
PRIMARY KEY (`id`),
KEY `from` (`from`),
KEY `to` (`to`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 AUTO_INCREMENT=1 ;
1) this table can be so much bigger, imagine 100 girls online all day speaking with each other, so is my index nice?
2) I have del_from_status and del_to_status, if, for example, user 1 delete all messages from user 2 I will
update del_to_status=1 where from=2 and to=1
I think your index looks okay given the UPDATE query you mention and the kind of SELECTs I imagine you will need to display a chat.
However, if you anticipate this table having many rows, I might optimise the fields to minimise the table size to maintain performance as it grows. sent could be TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP. I'm not clear on the purposes of recd, del_from_status and del_to_status but if they are only boolean values, you could use a BIT(1) field for these if your MySQL engine is modern enough; if not a BOOLEAN field.
It is also unusual to use from and to as field names as these are reserved SQL words. Combining all this, here is an alternative suggested CREATE TABLE:
CREATE TABLE `chat` (
`id` int(11) unsigned NOT NULL AUTO_INCREMENT,
`from_user` int(10) unsigned NOT NULL,
`to_user` int(10) unsigned NOT NULL,
`message` text NOT NULL,
`sent` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
`recd` tinyint(1) NOT NULL DEFAULT '0',
`del_from_status` tinyint(1) NOT NULL DEFAULT '0',
`del_to_status` tinyint(1) NOT NULL DEFAULT '0',
PRIMARY KEY (`id`),
KEY `from` (`from_user`),
KEY `to` (`to_user`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4;
Final thought - if you want to support group chats in the future you may wish to re-architect this. You could have a chat table which tells you which users are in a chat, and a chat_message table which contains message content related to the chat.id. You could add "since" and "until" timestamps to the chat table to support users joining and leaving group chats.