mysql choose between unique key and primary key for user id

Question

Im creating a user database ... i want to separate user - cellphone number from 'user' table and create another table for it (user_cellphone (table))

but i have a problem to select best index !

in user_cellphone table, we get user_id and cellphone number ... but all SELECT queries are more based on 'user_id' so i want to know if it's better to choose 'user_id' column as primary key or not !!!

(Also each user have only one cellphone number !)

which option of these 2 options are better ?

CREATE TABLE `user_cellphone_num` (
    `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
    `cellphone_country_code` SMALLINT UNSIGNED NOT NULL,
    `cellphone_num` BIGINT UNSIGNED NOT NULL,
    `user_id` INT UNSIGNED NOT NULL,
    PRIMARY KEY (`id`),
    UNIQUE INDEX `cellphone` (`cellphone_country_code`, `cellphone_num`),
    UNIQUE INDEX `user_id` (`user_id`)
)


CREATE TABLE `user_cellphone_num` (
    `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
    `cellphone_country_code` SMALLINT UNSIGNED NOT NULL,
    `cellphone_num` BIGINT UNSIGNED NOT NULL,
    `user_id` INT UNSIGNED NOT NULL,
    PRIMARY KEY (`user_id`),
    UNIQUE INDEX `id` (`id`),
    UNIQUE INDEX `cellphone` (`cellphone_country_code`, `cellphone_num`)
)

choosing 'user_id' as primary key or just set 'user_id' as a unique key ?! is there any different here in performance ? (Im talking about when i have millions of rows)

in future im going to use some queries like this:

select u.*,cell.* FROM user AS u LEFT JOIN user_cellphone AS cell ON cell.user_id = u.id

so which one of these options give me better performance for some queries like this ?

score 1 · Answer 1 · answered Mar 22 '20 at 21:32

May I offer some hard-won data design advice?

Do not use telephone numbers as any kind of unique or primary key.

Why not?

Sometimes multiple people use a single number.
Sometimes people make up fake numbers.
People punctuate numbers based on context. To my neighbors, my number is (978)555-4321. To a customer in the Netherlands it is +1.978.555.4321. Can you write a program to regularize those numbers? Of course. Can you write a correct program to do that? No. Why bother trying. Just take whatever people give you.

(Unless you work for a mobile phone provider, in which case ask your database administrator.

Read this carefully. https://github.com/google/libphonenumber/blob/master/FALSEHOODS.md

that's why i separated country code from cellphone number ... here (978) can be country code or can be part of number and (1) be the country code ... — ELHASKSERVERS, Mar 22 '20 at 21:34
@elhaskservers it's your choice to ignore the best advice you're going to get — Strawberry, Mar 22 '20 at 23:42

score 0 · Accepted Answer · answered Mar 22 '20 at 21:18

InnoDB tables are stored as a clustered index, also called an index-organized table. If the table has a PRIMARY KEY, then that is used as the key for the clustered index. The other UNIQUE KEY is a secondary index.

Queries where you look up rows by the clustered index are a little bit more efficient than using a secondary index, even if that secondary index is a unique index. So if you want to optimize for the most common query which you say is by user_id, then it would be a good idea to make that your clustered index.

In your case, it would be kind of strange to separate the cellphones into a separate table, but then make user_id alone be the PRIMARY KEY. That means that only one row per user_id can exist in this table. I would have expected that you separated cellphones into a separate table to allow each user to have multiple phone numbers.

You can get the same benefit of the clustered index if you just make sure user_id is the first column in a compound key:

CREATE TABLE `user_cellphone_num` (
    `user_id` INT UNSIGNED NOT NULL,
    `num` TINYINT UNSIGNED NOT NULL,
    `cellphone_country_code` SMALLINT UNSIGNED NOT NULL,
    `cellphone_num` BIGINT UNSIGNED NOT NULL,
    PRIMARY KEY (`user_id`, `num`)
)

So a query like SELECT ... FROM user_cellphone_num WHERE user_id = ? will match one or more rows, but it will be an efficient lookup because it's searching the first column of the clustered index.

Reference: https://dev.mysql.com/doc/refman/8.0/en/innodb-index-types.html

cellphone number is optional so i separated from user table to avoid extra memory usage (since it's 12-BYTE integer) and maybe for 50% of my users, it's 0 (NULL) for ever ... so i decided to separate it ... each user have only one number ... so with this condition, you say (PRIMARY KEY ('user_id')) is the best option ? — ELHASKSERVERS, Mar 22 '20 at 21:36
If you're trying to optimize for space, you can just use NULL. See my answer to https://stackoverflow.com/a/230923/20860. — Bill Karwin, Mar 22 '20 at 21:57
i read it but still for some performance reasons, i prefer to separate it .... i searched too much about this NULL thing ... — ELHASKSERVERS, Mar 22 '20 at 22:12

mysql choose between unique key and primary key for user id

2 Answers2