CODEWITHSUNDEEP
mysql
Yeti
Yeti

Reputation: 5818

What is the benefit of using int instead of bigint in this case?

(MYSQL n00b)

I have 3 tables:

id = int(10), photo_id = bigint(20)

PHOTO records limited to 3 million

PHOTO:
+-------+-----------------+
| id    | photo_num       |
+-------+-----------------+
| 1     | 123456789123    |
| 2     | 987654321987    |
| 3     | 5432167894321   |
+-------+-----------------+

COLOR:
+-------+-----------------+---------+
| id    | photo_num       | color   |
+-------+-----------------+---------+
| 1     | 123456789123    | red     |
| 2     | 987654321987    | blue    |
| 3     | 5432167894321   | green   |
+-------+-----------------+---------+


SIZE:
+-------+-----------------+---------+
| id    | photo_num       | size    |
+-------+-----------------+---------+
| 1     | 123456789123    | large   |
| 2     | 987654321987    | small   |
| 3     | 5432167894321   | medium  |
+-------+-----------------+---------+

Both COLOR and SIZE tables will have several million records.

Q1: Is it better to change photo_num on COLOR and SIZE to int(10) and point it to PHOTO's id?

Right now I use these: (PHOTO is no where in the picture)

SELECT * from COLOR WHERE photo_num='xxx';
SELECT * from SIZE WHERE photo_num='xxx';

Q2: How will the SELECT query look if PHOTO id was used in COLOR, SIZE?

Upvotes: 2

Views: 204

Answers (2)

Mark Byers
Mark Byers

Reputation: 837946

Q1: It is better to use an int as a foreign key, but far more important is to index the table correctly. If you have the correct indexes it probably will be good enough either way. For your query you need to make sure that photo_num is indexed on all tables.

I also wonder why you decided to split it up the table like this. Could a photo have more than one size or color? What is the purposes of the separate tables?

Q2: It will use a JOIN:

SELECT *
FROM color
JOIN photo
ON photo.id = color.photo_id
WHERE photo_num='xxx'

Upvotes: 1

Don
Don

Reputation: 9661

I'd go with:

PHOTO:
+-------+-----------------+----------+---------+
| id    | photo_num       | color_id | size_id |
+-------+-----------------+----------+---------+
| 1     | 123456789123    | 1        | 3       |
| 2     | 987654321987    | 1        | 2       |
| 3     | 5432167894321   | 2        | 2       |
+-------+-----------------+----------+---------+

COLOR:
+-------+---------+
| id    | color   |
+-------+---------+
| 1     | red     |
| 2     | blue    |
| 3     | green   |
+-------+---------+


SIZE:
+-------+---------+
| id    | size    |
+-------+---------+
| 1     | large   |
| 2     | small   |
| 3     | medium  |
+-------+---------+

And:

SELECT <Columns> FROM PHOTO JOIN COLOR ON PHOTO.color_id = COLOR.id JOIN SIZE ON PHOTO.size_id = SIZE.id

<Columns> perhaps being PHOTO.photo_num, COLOR.color, SIZE.size or what you need for the purpose.

Upvotes: 1

Related Questions