Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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.

share|improve this question

2 Answers 2

up vote 5 down vote accepted

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

share|improve this answer
    
The JSON encoded response from the Facebook API does not suggest any type. Here is an example: graph.facebook.com/ayushsobti –  xbonez May 1 '12 at 6:00
2  
I see in http://developers.facebook.com/docs/reference/api/user/ : "Fields. The User object has the following fields:" Name: id | Description: The user's Facebook ID | Permissions: No access_token required | Returns: **string** –  ypercube May 1 '12 at 9:04

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.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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