Should I use data type SERIAL for table id columns? I found out that there is a mysql datatype called SERIAL, it appears to be designed for use as an id column. However some of my peers don't think that the real data type BIGINT implied by SERIAL is appropriate for tables that may never get big. 

Is there any reason to avoid using SERIAL type for a table id column?

Are there any obvious caveats?
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 &lt; 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 &lt; POWER(256,1) (256), TINYINT UNSIGNED
id &lt; POWER(256,2) (65536), SMALLINT UNSIGNED
id &lt; POWER(256,3) (16777216), MEDIUMINT UNSIGNED
id &lt; POWER(256,4) (4294967296), INT UNSIGNED