Facebook's user id's go up to 2^32 .. which by my count it 4294967296.
mySQL's unsigned int's range is 0 to 4294967295 (which is 1 short - or my math is wrong) and its unsigned big int's range is 0 to 18446744073709551615
int = 4 bytes, bigint = 8 bytes
OR
Do I store it as a string?
varchar(10) = ? bytes
How will it effect efficiency, I heard that mysql handle's numbers far better than strings (performance wise). So what do you guys recommend
Because Facebook assigns the IDs, and not you, you must use BIGINTs.
Facebook does not assign the IDs sequentially, and I suspect they have some regime for assigning numbers.
I recently fixed exactly this bug, so it is a real problem.
I would make it UNSIGNED, simply because that is what it is.
I would not use a string. That makes comparisons painful and your indexes clunkier than they need to be.
You can't use INT any more. Last night I had two user ids that maxed out INT(10).
If you are reading this in 2015 when facebook has upgraded their API to 2.0 version. They have added a note in their documentation stating that their ids would be changed and would have an app scope. So maybe there is huge possibility later in the future that they might change all the ids to Alpha numeric.
https://developers.facebook.com/docs/apps/upgrading#upgrading_v2_0_user_ids So I would suggest to keep the type to varchar and avoid any future migration pains
I use a bigint to store the facebook id, because that's what it is.
but internally for the primary and foreign keys of the tables, i use a smallint, because it is smaller. But also because if the bigint should ever have to become a string (to find users by username instead of id), i can easily change it.
so i have a table that looks like this:
profile
- profile_key smallint primary key
- profile_name varchar
- fb_profile_id bigint
and one that looks like this
something_else
- profile_key smallint primary key
- something_else_key smallint primary key
- something_else_name varchar
and my queries for a singe page could be something like this:
select profile_key, profile_name
from profile
where fb_profile_id = ?
now i take the profile_key and use it in the next query
select something_else_key, something_else_name
from something_else
where profile_key = ?
the profile table almost always gets queried for almost any request anyway, so i don't consider it an extra step.
And ofcourse it is also quite ease to cache the first query for some extra performance.
Store them as strings.
The Facebook Graph API returns ids as strings, so if you want comparisons to work without having to cast, you should use strings. IMO this trumps other considerations.
Your math is a little wrong... remember that the largest number you can store in N bytes is 2^(N) - 1... not 2^(N). There are 2^N possible numbers, however the largest number you can store is 1 less that.
If Facebook uses an unsigned big int, then you should use that. They probably don't assign them sequentially.
Yes, you could get away with a varchar... however it would be slower (but probably not as much as you are thinking).
I would just stick with INT. It's easy, it's small, it works and you can always change the column to a larger size in the future if you need to.
FYI:
VARCHAR(n) ==> variable, up to n + 1 bytes
CHAR(n) ==> fixed, n bytes
Unless you expect more than 60% of the world's population to sign up, int should do?
