0

As below code, I set chat_id and student_id as primary key so that the same chat will be added for another student_id. When it goes to production, the record will be a lot. Should I add one more index for student_id only so that the search will be faster for every user when they come back to the screen to see recently messages?

CREATE TABLE `tim_chat_recipients` (
`chat_id` INT(11) NOT NULL,
`student_id` INT(11) NOT NULL,
`message_status` TINYINT(1) NOT NULL DEFAULT '0' COMMENT '1:New, 2:Read, 3:Deleted',
PRIMARY KEY (`chat_id`, `student_id`))COLLATE='latin1_swedish_ci' ENGINE=InnoDB;
user2864740
  • 60,010
  • 15
  • 145
  • 220
Joe Ijam
  • 2,123
  • 4
  • 19
  • 30
  • This is a 'compound primary key', *not* a 'shared primary key'. See the [information for a a 'shared primary key'](https://stackoverflow.com/tags/shared-primary-key/info) for details as to what the difference is. – user2864740 Dec 31 '17 at 05:10

1 Answers1

0

If the code is going to search for student_id without a chat_id then an index over at least student_id is required or queries will result in non-scaling table/cluster scans.. this is because the implicit cluster/index (chat_id, student_id) can't be used for index/reduction without a supplied chat_id.

This secondary index - INDEX(student_id, [optionally other columns]) - might include other columns, either as part of the index or includes, although "appropriate selection" depends a good bit on the queries. In this case it may make sense to include the message_status column to be able to use "messages not seen" as a filter with minimal read-IO as it can avoid a probe back to the table/cluster. (Indices are about balancing maintenance/disk costs and benefits to queries.)

In short: for an index to be selected by the Query Planner in a SARGABLE query, the left parts of the index must be known and mentioned in either the WHERE or, sometimes, the JOIN .. ON clauses.

user2864740
  • 60,010
  • 15
  • 145
  • 220