sql - Mysql table with composite index but not primary key

Question

I need a table to store some ratings, in this table I have a composite index (user_id, post_id) and other column to identify different rating system.

user_id - bigint
post_id - bigint
type - varchar
...

Composite Index (user_id, post_id)

In this table I've not a primary key because the primary need to be unique and the INDEX not need to be unique, in my case univocity is a problem.

For example I can have

INSERT INTO tbl_rate
    (user_id,post_id,type)
VALUES
    (24,1234,'like'),
    (24,1234,'love'),
    (24,1234,'other');

The missing of PRIMARY KEY may cause performance problem? My table structure is good or I need to change it?

Thank you

Answer 1

A few points:

It sounds like you are just using what is currently unique about the table and making that as a primary key. That works. And natural keys have some advantages when it comes to querying because of locality. (The data for each user is stored in the same area). And because the table is clustered by that key which eliminates lookups to the data if you are searching by the columns in the primary.

1. But, using a natural primary key like you chose has disadvantages for performance as well.

2. Using a very large primary key will make all other indexes very large in innodb because the primary key is included in each index value.

3. Using a natural primary key isn't as fast as a surrogate key for INSERT's because in addition to being bigger it can't just insert at the end of the table each time. It has to insert in the section for that user and post etc.

4. Also, if u are searching by time most likely you will be seeking all over the table with a natural key unless time is your first column. surrogate keys tend to be local for time and can often be just right for some queries.

5. Using a natural key like yours as a primary key can also be annoying. What if you want to refer to a particular vote? You need a few fields. Also it's a little difficult to use with lots of ORMs.

Here's the Answer

I would create your own surrogate key and use it as a primary key rather than rely on innodb's internal primary key because you'll be able to use it for updates and lookups.

ALTER TABLE tbl_rate 
ADD id INT UNSIGNED NOT NULL AUTO_INCREMENT, 
ADD PRIMARY KEY(id);

But, if you do create a surrogate primary key, I'd also make your key a UNIQUE. Same cost but it enforces correctness.

ALTER TABLE tbl_rate 
ADD UNIQUE ( user_id, post_id, type );