I have a table of users but I don't know what is the best practice between using a unique MEDIUMINT identifier or a unique pseudonyme identifier using VARCHAR.
With MEDIUMINT identifiers, like the users table is linked to a lot of others tables (news, comments, contributions, etc.), you are forced to make a JOIN to this table to retrieve the pseudonyme of the user each time you are using those tables (= really often). It seems to me an useless costly step to do, a step that could be resolved by repeating directly in those tables the highly requested pseudonyme.
The advantage of using an integer is to be able to modify pseudonymes when you want (like they are stored at only one place) but you know that websites (and mine) tends to prevent users to modify their own pseudos to keep the identity of everyone stable. And if you really need to, you can update those foreign keys with ON UPDATE CASCADE.
If you don't need to do a JOIN, with a pseudo identifier using VARCHAR, those columns will take more storage like a MEDIUMINT costs 3 bytes but a VARCHAR will cost at best 4 bytes (you can't have a pseudo with less than 3 characters, and more than 20) and I think something like 61 bytes if the user wants 20 kanji. Averagely, I think it will be twice what needs a MEDIUMINT identifier. Moreover, in my opinion, this solution appears to me to be really disgraceful compared to nice MEDIUMINT foreign keys even if I am currently using it...
So, what is supposed to be the best practice?
Does the storage cost overcomes the constraint and drop of performance or doesn't the JOIN degrade the performance enough to care about it when the storage cost is greedy in comparison? Or maybe both are valid because criteria are balanced? Or maybe are there others parameters I didn't take into account?
Thank you for your help.
Note: I am using Mysql.
Like everyone here seems to think JOIN are better, I remembered starting with JOIN then tried without them and switched to that solution without JOIN after I saw the JOIN overload. My worst/best case scenario was a table with 120k users who can add 50 favorites max each, what gave me a table of 6M favorites. My query was to find for 1 element (the thing you can fav), which users had faved it. Here are the tables filled with random data:
-VARCHAR WITHOUT JOIN
CREATE TABLE `membres_fav2` ( `ps` varchar(20) NOT NULL, `id_l` mediumint(8) unsigned NOT NULL, `dt` datetime NOT NULL, PRIMARY KEY (`ps`,`id_l`), KEY `id_l` (`id_l`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8
id_l is the id of the element faved.
SELECT ps FROM membres_fav2 WHERE id_l=?;
-MEDIUMINT WITH JOIN
CREATE TABLE `membres_id` ( `id` mediumint(6) unsigned NOT NULL AUTO_INCREMENT, `ps` varchar(20) NOT NULL, ... other data PRIMARY KEY (`id`), UNIQUE KEY `idx_membres_id_ps` (`ps`) ) ENGINE=InnoDB AUTO_INCREMENT=120096 DEFAULT CHARSET=utf8 CREATE TABLE `membres_fav_i` ( `id` mediumint(6) unsigned NOT NULL, `id_l` mediumint(8) unsigned NOT NULL, `dt` datetime NOT NULL, PRIMARY KEY (`id`,`id_l`), KEY `mfi_id_l` (`id_l`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8
SELECT mi.ps FROM membres_fav_i as mfi JOIN membres_id as mi WHERE mfi.id=mi.id AND mfi.id_l=?;
(UPDATE > innodb_buffer_pool_size=3000)
I couldn't use benchmark but for 50 tests on different values (the same for both), I got:
-QUERY WITHOUT JOIN gave me most of the time 0s, sometimes 0.016s. I did RESET QUERY CACHE; and used sql_no_cache but I guess that means it's a really easy statement.
-QUERY WITH JOIN gave me 0,42522s.
With a JOIN, it's almost half a second longer. How is it acceptable?
membres > '121.290.752' (varchar which doesn't have PK id column)
membres_id > '108.642.304' > -12.648.448 (mediumint)
membres_fav2 > '292.487.168' (varchar)
membres_fav_i > '191.578.112' > -100.909.056 (mediumint)
membres > '0' (PK ps) (uh, why 0?)
membres_id > '3.686.400' > +3.686.400 (PK id, UNI ps)
membres_fav2 > '148.602.880' (PK ps,id_l, KEY id_l)
membres_fav_i > '128.598.016' > -20.004.864 (PK id,id_l, KEY id_l)
With a JOIN, you make a profit of 129mo for a loss of 0,43s. Almost half a second on a page with a lot of other requests, that's why I am a bit confused.
Aren't huge websites like flickr or twitter using pseudonyme as identifier instead of integer or is it just url rewriting cosmetic?