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

Posted by Yeti on Stack Overflow See other posts from Stack Overflow or by Yeti
Published on 2010-06-10T11:21:12Z Indexed on 2010/06/10 11:32 UTC
Read the original article Hit count: 254

Filed under:

(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?

© Stack Overflow or respective owner

Related posts about mysql