CodeDump
Rick Joe Rick Joe - 1 year ago 46
SQL Question

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
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?

Neil Mukerji Neil Mukerji
Answer

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.

Share