pkey in mysql database table

Question

From this SQL Statement, I'm trying to understand the use of pkey, we already have composite key as primary key, Can someone please explain this.

CREATE TABLE categories_products (
    category_id int unsigned not null,
    product_id int unsigned not null,
    PRIMARY KEY (category_id, product_id),
    KEY pkey (product_id),
    FOREIGN KEY (category_id) REFERENCES categories (id)
       ON DELETE CASCADE
       ON UPDATE CASCADE,
    FOREIGN KEY (product_id) REFERENCES products (id)
       ON DELETE CASCADE
       ON UPDATE CASCADE
);

Answer 1

By way of extended explanation, and going from your example:

First, lets set up the environment:

mysql>
mysql> create table categories_products (
    ->   category_id int unsigned not null,
    ->   product_id int unsigned not null,
    ->   somefield varchar(25),
    ->   primary key (category_id, product_id)
    -> );
Query OK, 0 rows affected (0.10 sec)

mysql>
mysql> insert into categories_products(category_id, product_id) values (1, 1), (1, 2), (2, 2);
Query OK, 3 rows affected (0.05 sec)
Records: 3  Duplicates: 0  Warnings: 0

I've removed the foreign keys because they make no difference to this, and i've added one additional field because it makes the results slightly easier to explain. I'll expand on that after.

First we try querying for a particular category:

mysql> explain select * from categories_products where category_id = 1;
+----+-------------+---------------------+------+---------------+---------+---------+-------+------+-------+
| id | select_type | table               | type | possible_keys | key     | key_len | ref   | rows | Extra |
+----+-------------+---------------------+------+---------------+---------+---------+-------+------+-------+
|  1 | SIMPLE      | categories_products | ref  | PRIMARY       | PRIMARY | 4       | const |    2 |       |
+----+-------------+---------------------+------+---------------+---------+---------+-------+------+-------+
1 row in set (0.00 sec)

and we can see from the result that it is indeed using the PRIMARY key, and examining only 2 rows, because the index knows that's all that matches the where constraint.

Our next test queries both product AND category:

mysql> explain select * from categories_products where category_id = 1 and product_id = 1;
+----+-------------+---------------------+-------+---------------+---------+---------+-------------+------+-------+
| id | select_type | table               | type  | possible_keys | key     | key_len | ref         | rows | Extra |
+----+-------------+---------------------+-------+---------------+---------+---------+-------------+------+-------+
|  1 | SIMPLE      | categories_products | const | PRIMARY       | PRIMARY | 8       | const,const |    1 |       |
+----+-------------+---------------------+-------+---------------+---------+---------+-------------+------+-------+
1 row in set (0.00 sec)

and once again we can see that the PRIMARY key is being used, but this time its even better, it's only returning one row.

Next, lets try looking at just a product:

mysql> explain select * from categories_products where product_id = 1;
+----+-------------+---------------------+------+---------------+------+---------+------+------+-------------+
| id | select_type | table               | type | possible_keys | key  | key_len | ref  | rows | Extra       |
+----+-------------+---------------------+------+---------------+------+---------+------+------+-------------+
|  1 | SIMPLE      | categories_products | ALL  | NULL          | NULL | NULL    | NULL |    3 | Using where |
+----+-------------+---------------------+------+---------------+------+---------+------+------+-------------+
1 row in set (0.00 sec)

This time, the query was unable to find a suitable index to use, so had to narrow the results using where. This is less efficient than if it were able to perform an index lookup.

Why is this the case? Why could the optimiser use category_id but not product_id even tho they are both in the composite index? Because MySQL reads that index from left to right. Consider a large composite index, (f1, f2, f3, f4). This implicitly gives you access to these additional indexes, (f1), (f1, f2), (f1, f2, f3).

Now lets add your pkey index in there and see what happens.

mysql> create index pkey on categories_products(product_id);
explain select * from categories_products where product_id = 1;
Query OK, 0 rows affected (0.17 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql>
mysql> explain select * from categories_products where product_id = 1;
+----+-------------+---------------------+------+---------------+------+---------+-------+------+-------+
| id | select_type | table               | type | possible_keys | key  | key_len | ref   | rows | Extra |
+----+-------------+---------------------+------+---------------+------+---------+-------+------+-------+
|  1 | SIMPLE      | categories_products | ref  | pkey          | pkey | 4       | const |    1 |       |
+----+-------------+---------------------+------+---------------+------+---------+-------+------+-------+

And there we go, the same query as before is now able to be performed using an index as well.

And now to why I added an additional field. When the primary key covers the entire table, as it did in this case, any query is going to read the entire result from the index, rather than the table, and this may give a somewhat misleading entry in the explain results. For example if i remove that additional field, and the pkey index, and rerun the query, these are the results:

mysql> explain select * from categories_products where product_id = 1;
+----+-------------+---------------------+-------+---------------+---------+---------+------+------+--------------------------+
| id | select_type | table               | type  | possible_keys | key     | key_len | ref  | rows | Extra                    |
+----+-------------+---------------------+-------+---------------+---------+---------+------+------+--------------------------+
|  1 | SIMPLE      | categories_products | index | NULL          | PRIMARY | 8       | NULL |    3 | Using where; Using index |
+----+-------------+---------------------+-------+---------------+---------+---------+------+------+--------------------------+
1 row in set (0.00 sec)

Now at first glance it seems that the PRIMARY index is being used, but on closer examination we see it is still examining the whole table (3 rows), and limiting the result with where, as well as the index.

Anyway, this is why you need the index on product_id