CODEWITHSUNDEEP
mysqlperformancedatabase-performanceunique-constraintunique-key
Anton Dozortsev
Anton Dozortsev

Reputation: 4892

MySQL Unique Key instead of PK

I have two tables message and hashtag:

enter image description here

One message can have multiple hashtags. But without duplicates of hashtags for one message.

message table: enter image description here

hashtag table: enter image description here

In table hashtag we have to keys, one PRIMARY for id and another UNIQUE key (hashtag, message_id).

Questions:

Does this make sense use id column in hashtag table in case we have unique pairs of (hashtag message_id)?

In case if id it's not necessary, are there some performance issues or other drawbacks?

Upvotes: 0

Views: 542

Answers (2)

Rick James
Rick James

Reputation: 142306

If you have a "natural" PRIMARY KEY, it is OK (and often favorable) to avoid adding an AUTO_INCREMENT (or 'sequence').

If 1:many:

CREATE TABLE hashtag (
    hashtag VARCHAR(6) NOT NULL,
    message_id INT     NOT NULL,
    PRIMARY KEY(hashtag),
    INDEX      (message_id, hashtag)
);

For many:many

CREATE TABLE hashtag (
    hashtag VARCHAR(6) NOT NULL,
    message_id INT     NOT NULL,
    PRIMARY KEY(hashtag, message_id),
    INDEX      (message_id, hashtag)
);

Further discussion: http://mysql.rjweb.org/doc.php/index_cookbook_mysql#many_to_many_mapping_table

In MySQL, use ENGINE=InnoDB, and always have an explicit PRIMARY KEY. Keep in mind that a PK is a UNIQUE key, which is an INDEX.

Another issue... INT is always 4 bytes. VARCHAR(6) (assuming English text) takes between 1 and 7 bytes. Guess what? VARCHAR(6) may be smaller, on average. Hence, it could be wasteful in both space and speed to assign an INT id for each hashtag!

Upvotes: 1

LoztInSpace
LoztInSpace

Reputation: 5697

You could be right in that you want a unique constraint on ID/message ID.

However, without knowing exactly, I think a better design would be

HASHTAG (ID, HASTAG_TEXT) ID as PK

MESSAGE (ID, MESSAGE_TEXT) ID as PK

HASTAG_TO_MESSAGE(HASHTAG_ID, MESSAGE_ID) Both as PK

You might want a "sequence #" too, in which case you'd want a PK on all columns and a unique constraint on both IDs.

Upvotes: 0

Related Questions