Category: database

All database tips and tricks, installation, setup, configuration, optimizations tutorials

BigInt or Varchar for a large number I won’t ever sort / calculate on

BigInt or Varchar for a large number I won’t ever sort / calculate on

I need to store users' Facebook IDs in a column in my DB. 
The ID always consists of only digits, and can be as large as a number in excess of 500 million. Considering that I won't ever need to do any arithmetic, sorting or use relational operators on the values, what would be a better choice: BigInt or varchar ? 
The value will always be unique and never non-existent (null)
The only operation I do is check for the existence of an ID in the database, i.e. the = operator.

Solutions/Answers:

Answer 1:

varchar adds overhead:

  • length of the string needs stored (extra 2 bytes IIRC in MySQL) per field and in the index
  • requires more processing for collation on comparison

Saying that, what does the Facebook API describe the type as? Use the “native” one of course

Answer 2:

According to the MySQL Documentation on BIGINT

BIGINT[(M)] [UNSIGNED] [ZEROFILL] A large integer. The signed range
is -9223372036854775808 to 9223372036854775807. The unsigned range is
0 to 18446744073709551615.

SERIAL is an alias for BIGINT UNSIGNED NOT NULL AUTO_INCREMENT UNIQUE.

Some things you should be aware of with respect to BIGINT columns:

■ All arithmetic is done using signed BIGINT or DOUBLE values, so you
should not use unsigned big integers larger than 9223372036854775807
(63 bits) except with bit functions! If you do that, some of the last
digits in the result may be wrong because of rounding errors when
converting a BIGINT value to a DOUBLE.

MySQL can handle BIGINT in the following cases:

◦ When using integers to store large unsigned values in a BIGINT
column.

◦ In MIN(col_name) or MAX(col_name), where col_name refers to a
BIGINT column.

◦ When using operators (+, -, *, and so on) where both operands are
integers.

■ You can always store an exact integer value in a BIGINT column by
storing it using a string. In this case, MySQL performs a
string-to-number conversion that involves no intermediate
double-precision representation.

■ The -, +, and * operators use BIGINT arithmetic when both operands
are integer values. This means that if you multiply two big integers
(or results from functions that return integers), you may get
unexpected results when the result is larger than 9223372036854775807.

As you stated, as long as you do not do any arithmetic, sorting or use relational operators on the values, I would go with BIGINT.

The documentation just mentioned storing the number as a string to bypass intermediate steps done through double precision representation.

Our Awesome Free Tools

References

Loading...