4

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?

4

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 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
  • 1
    Is the avoidance of this bloat premature optimization? Or sister question; Is increasing the size of an Int column inconsequential? – ThorSummoner Nov 13 '14 at 21:11
  • 2
    If you cannot forecast the number of rows the table will have, then no it is not premature. As for the second question, at the risk of sounding redundant, smaller numeric keys are always faster to process than larger numeric keys. This becomes more obvious should ever run OPTIMIZE TABLE due to large tables that experience INSERTs, UPDATEs, and DELETEs. – RolandoMySQLDBA Nov 13 '14 at 21:32
  • 1
    This is perfect example of premature optimisation. Adding few bytes per row for a table that will always have a tiny number of rows is irrelevant. And if you don't know that the table will have a tiny number of rows, than you don't want this "optimisation" anyway. – Davor Nov 24 '17 at 12:35

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.