0

I am creating a database in where I will need to give items a unique value. I was thinking about using BIGINT while padding left with 0s as I auto_increment.

Does it take longer to search for the number 1 id vs 0000000001 or is the same? I think it would be the same as each column is compared with whatever the given number was.

Also I understand that using a BIGINT takes up more room but being that the item ids could get bigger than the approx to 2 billion limit on an INT I decided to use BIGINT. Is this a poor way of hanlding this situation or is there an industry standard I should be following?

retroCheck
  • 39
  • 1
  • 8
  • Why don't you use unsigned integer? – Malay M Sep 03 '15 at 23:03
  • Pretty optimistic that you'll need more than 2 billion records :) Unsigned INT and not padded should be perfectly fine. – timgavin Sep 03 '15 at 23:05
  • 1
    It depends on how much data you expect to store in your table. `INT UNSIGNED` can hold values from 0 to 4,294,967,295 and most time that's enough. `BIGINT` can hold values from -9,223,372,036,854,775,808 to -9,223,372,036,854,775,807 and `BIGINT UNSIGNED` can hold values from 0 to 18,446,744,073,709,551,615. It's up to you to decide – Barranka Sep 03 '15 at 23:07
  • How does unsigned auto_increment work together? – retroCheck Sep 03 '15 at 23:09
  • Possible duplicate with your second part [here](http://stackoverflow.com/questions/2124631/sql-server-int-or-bigint-database-table-ids). Also, why do you want to pad the ID with zeros? most likely you would want to keep the id with your own without revealing it to the front-end. But anyways searching would generally take the same time in both cases. – Saehun Sean Oh Sep 03 '15 at 23:11

0 Answers0