CODEWITHSUNDEEP
mysql
Koerr
Koerr

Reputation: 15733

Mysql covering index optimization

Table(InnoDB):

CREATE TABLE `product_category` (
  `product_id` int(11),
  `category_id` int(11),
  PRIMARY KEY (`product_id`,`category_id`),
  UNIQUE KEY `category_id` (`category_id`,`product_id`)
)

I need run the follow SQL queries in my project:

1. select `category_id` from `product_category` where `product_id`=?
2. select `product_id` from `product_category` where `category_id`=?

I create PRIMARY KEY(product_id,category_id) for the SQL query 1

and I create UNIQUE(category_id,product_id) KEY for the SQL query 2

I want to know that right?

any others optimization possible?

thanks a lot

Upvotes: 0

Views: 309

Answers (2)

Lajos Arpad
Lajos Arpad

Reputation: 77002

You shouldn't create the unique constraint for those fields if you already have a primary key for them (if a set of columns is primary key, then the set is also unique).

You can set the fields to be unique separately, but only if you are sure that a category_id has a single product_id and a product_id has a single category_id. From the names I deduce that a category probably has more products and a product may be part of more categories. If this is the case you shouldn't create unique constraints at all.

Optimization: If you don't have so many categories or products you should define these fields to be of smaller size (maybe int(5)).

Upvotes: 1

a1ex07
a1ex07

Reputation: 37382

If you are using INNODB engine, there is no need for second composite index (on category_id,product_id). It's enough to have an index just on category_id because all secondary indexes keep a copy of primary key

Upvotes: 2

Related Questions