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 ;
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.