Clustered index for foreign key column-CodePudding

Consider the following example of a messaging system:

create table chat_group
(
    id           int auto_increment primary key,
    title        varchar(100)         not null,
    date_created date                 not null
)


create table chat_message
(
    id                int auto_increment,
    user_id           int                  not null,
    chat_group_id     int                  not null,
    message           text charset utf8mb4 not null,
    date_created      datetime             not null
)

Now I see that the most common request for the chat_message table is SELECT * FROM chat_message where chat_group_id = ?. So my idea is to put a clustered index on chat_group_id column so the chat messages will be organized by groups on the disk.

But in MySQL it requires PRIMARY KEY(which actually is a clustered index) to be unique, so what is the solution here? What clustered index do I make for the given situation.

CodePudding user response：

Yes, "you can have your cake and eat it, too":

PRIMARY KEY(chat_group_id, id),
INDEX(id)

The PK provides "clustering" by the group; this is likely to speed up your main queries. Including id makes it UNIQUE, which is a requirement (in MySQL) for the PK.

The secondary INDEX(id) is the minimum needed to keep AUTO_INCREMENT happy -- namely having some index starting with the id.