patternsqlMinor
Is using strings as keys for reference to other tables bad in terms of memory usage?
Viewed 0 times
referencetablesbadkeysusagetermsforusingmemoryother
Problem
So i have to deal with the following scenario:
I have a table , users , which holds all of the users of my application .
Each user has an email which is unique and thus could be the primary key of said table .
Let's now say i have a second table that associates some data with a user . What pops into my mind is to have that second table hold an email column which i can then use to associate a row with a user .Is this approach bad in terms of memory? Will MySQL store said email repeatedly for each extra table that holds an email column to reference users table? Should i instead just use an auto-increment id for each user and use that for the whole reference thing since an integer is way lighter than a string big enough to hold an email?
I have a table , users , which holds all of the users of my application .
Each user has an email which is unique and thus could be the primary key of said table .
Let's now say i have a second table that associates some data with a user . What pops into my mind is to have that second table hold an email column which i can then use to associate a row with a user .Is this approach bad in terms of memory? Will MySQL store said email repeatedly for each extra table that holds an email column to reference users table? Should i instead just use an auto-increment id for each user and use that for the whole reference thing since an integer is way lighter than a string big enough to hold an email?
Solution
Yes MySql and all other rdms will store the complete email as varchar and reserve space for the number of bytes it needs or the maximal size depending on the rdms.
Integer with up to 8 bytes for a big integer will only use these bytes, and are so faster when referencing.
In terms of speed you use INTEGER, and consider other like varchar(36) for uuids when the need arises, for example different servers have to save data in the same table.
with emails a unique and so be indexed for referencing, you should take the extra mile and use Integer if you expect you will have big tables.
Integer with up to 8 bytes for a big integer will only use these bytes, and are so faster when referencing.
In terms of speed you use INTEGER, and consider other like varchar(36) for uuids when the need arises, for example different servers have to save data in the same table.
with emails a unique and so be indexed for referencing, you should take the extra mile and use Integer if you expect you will have big tables.
Context
StackExchange Database Administrators Q#289995, answer score: 6
Revisions (0)
No revisions yet.