SQL 2 column primary key but want to search on one of those columns should I make it a separate key?

Question

What I have currently is:

CREATE TABLE IF NOT EXISTS members (
    user_id        INTEGER UNSIGNED NOT NULL,
    group_id       INTEGER UNSIGNED NOT NULL,
    status         TINYINT DEFAULT 1,
    role           TINYINT DEFAULT 0,
    created        TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    CONSTRAINT mem_pk PRIMARY KEY (user_id, group_id),
    KEY (status)
) ENGINE=InnoDB DEFAULT CHARACTER SET utf8;

I assign users to groups (one line for a unique user and group combo), but I want to be able to search for user_id and group_id individually so should I add keys for the fields:

KEY (user_id),
KEY (group_id)

Will I gain anything in doing so?

In fact I will never search for user_id and group_id together so do I even need the Primary Key?

Also any comments on this tables performance when tens of millions of rows?

Answer 1

Primary key are the columns which define uniqueness - in your case it seems they are user_id + group_id. User_id+group_id key can be used to search by user_id (first column), and you can add separate index only for group_id.