Should I normalise this database?

Question

I have two database designs thought of for storing messages(instant messages) between users. It will be a group chat system.

I have two table designs in mind.

One is this:

CREATE TABLE `mssg_grp_user` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `user_id` int(10) unsigned NOT NULL,
  `grp_id` int(10) unsigned NOT NULL,
  `body` varchar(100) COLLATE utf8_unicode_ci NOT NULL,
  `created_at` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
  PRIMARY KEY (`created_at`,`grp_id`,`user_id`,`id`),
  KEY `mssg_grp_user_user_id_foreign` (`user_id`),
  KEY `mssg_grp_user_grp_id_foreign` (`grp_id`),
  KEY `mssg_id_is` (`id`),
  CONSTRAINT `mssg_grp_user_grp_id_foreign` FOREIGN KEY (`grp_id`) REFERENCES `groups` (`id`) ON UPDATE CASCADE,
  CONSTRAINT `mssg_grp_user_user_id_foreign` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON UPDATE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=350201 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci

And another is this:

in the second one: the Messages table has the following keys

alter table messages 
drop primary key ,
add primary key (created_at, id),
add INDEX mssg_id_ix (id)    ; 

And there is a primary key of (grp_id,mssg_id) in the mssg_grps table.

My main queries are to retrieve the newest 50 messages that are in a list of group IDs AND are newer than a certain time. For the 2nd one, I ran this query :

SELECT * FROM test.mssg_grp a LEFT JOIN test.messages b ON a.mssg_id = b.id
Where b.created_at >"1970-01-03 19:08:21"
AND a.grp_id IN(8,2,1,2,5,6,3)
order by created_at limit 50;

This took a long time. It took 0.1 seconds for one query (there are 200k messages) and around 2 minutes for 100 concurrent users.

Then I decided to merge the tables(i.e. change my db schema) and switched to the first one.

To get the same data, i ran this query:

Select sql_no_cache * from test.mssg_grp_user where  grp_id IN (8,1,5,6,7,8,9) and created_at>888880   
order by  created_at desc limit 50;

This took 0.1 seconds for 100 concurrent users. This is obviously much faster. My questions are:

• Why is the join+order taking so much time?
• Should I switch to the first design.(the body field may have up to 500 characters)

Answer 1

The first solution is clearly more efficient than the second one, because you don't need to do the join (every join slows down a query, more or less depending on the access plan generated by the system).

More, I think the second solution has some problem, since from the schema it seems that you are using the column id inside mssg_grp as foreign key referencing the table mssg, while in that table you use as primary key the couple of columns created_at and id, which conflicts with the definition of the foreign key, from which one could assume that id is already a primary key for mssg.

So, if, as it seems from the diagram, the relation between mssg and mssg_grp is 1:1, you should opt for the first solution which is still normalized and more efficient.

Answer 2

1) left join is slower than inner join. It is first reason. The second add special indexes for optimization query. Use explain plan of query

2)if you want to use result table then I recommend you use material view. It will be faster and you can don't think about length of fields.

p.s. View is not a table so you will save you time instead your insert in third table