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
| improve this answer | |
0

There is another flaw with SERIAL that I've seen repeatedly but never been able to trace down. The increments aren't by 1. Often they are by 4-7. So you'd get ID's of 1, 5, 11, 18, 22, etc missing all the intermediate values.

| improve this answer | |
  • 4
    One possible reason is transactions that have rolled back. – Lennart Aug 20 at 18:41
-2

IMHO don't ever use 'serial'. The reason is that serial doesn't produce incremental increases. The first serial may be '1', but the second is likely '4' and the third may be '11'. If that doesn't matter, have at it. There is no downside (anymore) to using indices which are significantly too large. We don't run on machines with memory measured in K anymore. Don't adopt old axioms which aren't applicable anymore.

| improve this answer | |

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.