My schema is something like below -

CREATE TABLE `products` (
    `account_id` varchar(100) NOT NULL,
    `product_id` int NOT NULL,
    `product_name` varchar(100) NOT NULL,
    PRIMARY KEY (`account_id`,`product_id`),
    KEY `idx_product_id` (`product_id`)
) 

The app will have several accounts (customers) and each customer will have their own products, like a multi-tenant ecom site. A product by itself means nothing and it only makes sense if tied to an account_id. Account A (Customer A) can have product 1 and Account B can also have product 1. But the combination of Account and Product should be unique, hence the primary key is on account_id and product_id.

I know that there is no need for an index on account_id, as the primary key will create an index on the account_id/product_id combination. I am thinking that there is also no separate need for an additional index on product_id, as a product will always be referenced in the context of an account and the primary key index can address this.

1. Can I go ahead and remove KEY idx_product_id (product_id) from the table schema above?

2. In general, do we need an index on the second column of compound primary key?

CodePudding user response：

Think of a telephone book. It's like an index on (last_name, first_name). If you look up a person by their last name, it's very helpful that it's sorted by last name first. If you look up a person by both last name and first name, it is also helpful.

But if you use the phone book to try to look up someone only by their first name, the book doesn't help. It's not sorted by first name. Is this a problem? Would you ever look up someone by their first name? If you need to do that, you would need a second phone book sorted by first name. But if you never need to do that kind of lookup, you don't need it.

It's the same for secondary indexes. If you always look up your products by account and then by product, then the sort order of the primary key is enough. If you sometimes look up products to see which accounts use that product, it would help to have the secondary index with product_id first.

So the answer to your question depends on the queries you intend to run.