What is the benefit of using int instead of bigint in this case?
- by Yeti
(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?
