What is best for a primary key? Bigint of 20 numbers or varchar / varbinary of 15 characters? I have already seen some posts making comparisons of varchar with numeric fields, but most only compare with equal values ​​and do not talk about performance in primary keys.
I have a user registration table where each user needs to have a unique code and the auto-incrementing sequential numbers (1,2,3 ...) are not a good idea.
So I'm using PHP's uniquid() command to generate user codes.
Something like this: it5f29b715cff42 containing on average 13 to 15 characters in varchar type fields
I use this code in the primary key because it is the main code of the user's registration, and there will always be a user search for this code in the table.
Some people have suggested switching to a varbinary type field to gain performance. And yet I still don't know if it's the best idea.
So I thought of turning this code into numbers using PHP's hexdec() command. Leaving the code more or less like this: 10001674124319326018 containing an average of 16 to 20 characters. And storing that code in a bigint type field.
I know that the bigint field will always be 8 bytes, but how many bytes would varchar and varbinary in that number of characters occupy? What is the best option to use as a primary key in a table with an average of 2 billion records?
Bignint is much faster than cvarchar or varbinary. at anbay time..
And so you can have 18446744073709551615 (unsigned) Rows.
You would use uuids, when you enter data in different databases and try to unite them.
but if you only need unique ids, use bigint.