Should I use data type SERIAL for table id columns?

The only real concern would be size.

If a table is really small, why bloat it ? For example, if you have a table will never surpass 255 rows, use TINYINT UNSIGNED for id. No need to bloat the table's column up to 8 times bigger. This would also apply to the primary key.

After loading a table with data, you should run this

SELECT id FROM tablename PROCEDURE ANALYSE();

For any table < 4,294,967,296 rows, PROCEDURE ANALYSE() will never recommend a BIGINT. In terms of byte width, smaller numeric keys are always faster to process than larger numeric keys.

What will be further victimized would be indexes. Why ?

• In the Clustered Index (a.k.a. gen_clust_index), row data and BTREE for Primary Key occupy the same space
• InnoDB has a fixed size for an InnoDB Page

In light of these two things, the larger the Primary Key, the more space is needed and the more likely extra InnoDB pages are needed.

Therefore, for a populated table, my suggestion would be to let PROCEDURE ANALYSE() tell the correct dataytpe. For an empty table, try to forecast the number of value to expect and this set the data type to the following:

• id < POWER(256,1) (256), TINYINT UNSIGNED
• id < POWER(256,2) (65536), SMALLINT UNSIGNED
• id < POWER(256,3) (16777216), MEDIUMINT UNSIGNED
• id < POWER(256,4) (4294967296), INT UNSIGNED