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