1

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: enter image description here

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)
  • 1
    Please show EXPLAIN output. – philipxy Jan 10 '16 at 17:16
  • 1
    The left join in the first query is wrong - when you check any left-joined column in the WHERE clause, you turn it into an inner join (because a NULL returned from the join won't satisfy any conditions). And it seems you order by a column from the second table too, so if you fix the conditions, you get NULLs to sort by.. When you fix these, just use EXPLAIN to see the differences directly. – jkavalik Jan 10 '16 at 19:00
  • could you tell me the correct query for the join please ?I want to retrieve the 50 newest messages that belong to a groupid – harveyslash Jan 11 '16 at 6:18
  • 50 for each groupid or 50 together for all listed groupids? – jkavalik Jan 11 '16 at 7:53
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.

  • that id field in mss_grp is not a primary key. you can ignore it. I forgot to mention that. Should I still follow the first schema , or the second ? – harveyslash Jan 11 '16 at 4:20
  • But how is established the relation between mssg and mssg_grp then? Through which foreign key? Is the relation 1:1 or 1:N? If the relation is 1:1 then you can follow the first schema. – Renzo Jan 11 '16 at 7:58
  • mssg_id of the mssg_grp user is the FK on id of the messages column. this id field of the mssg column is auto increment, so will be unique. – harveyslash Jan 11 '16 at 8:10
  • So id in mssg is a primary key, as I said in my answer. The other question is: for each mssg_grp record you have one and only one record in mssg and viceversa? Or you have one or more records in mssg_grp the have the same mssg_id (so more than one corresponding message)? – Renzo Jan 11 '16 at 8:15
  • 2
    Ok, in this case you can use the first solution, with a single table, with the confidence that it is both normalized and efficient. – Renzo Jan 11 '16 at 8:18
0

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

  • 4
    You claim that "left join is slower than inner join".How is this justified? – ypercubeᵀᴹ Jan 10 '16 at 20:01
  • Yes. I read query. Make left join and then make condition that left field is required - it is inner join. Left join slow because query join all data which not need and then ignored - so it is lost resources. Please correct me if i am wrong. – Otabek Kasimov Jan 11 '16 at 19:55

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.