chat messager mysql optimizations

Question

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

but I need to scan all the table to do this, searching for each message from=2 to=1 and del_to_status=0. it can be slow if I have many messages, can't it? any suggestions for this?

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

chat messager mysql optimizations

Sign up