2

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.

EDIT

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.

QUERY:

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

QUERY

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?

Data_length of
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)

Index_length of
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?

  • 6
    Don't be afraid of joins or assume that they'll be inefficient. This is what we have indexes for. – Aaron Bertrand Feb 14 '16 at 0:15
  • Ok, so, question updated with a concrete example. – Some_user Feb 18 '16 at 0:57
3

So, what is supposed to be the best practice if both are not valid because criteria are balanced?

The criteria aren't likely balanced. They're almost never going to be balanced. The cost of the extra join to look up the surrogate key (that's the MEDIUMINT; a surrogate key is an opaque value, typically implemented as an auto-increment in MySQL, which has no intrinsic meaning outside the database and is ideally invisible to the user) is going to be negligible compared to the large storage, larger indexes (which means fewer rows per index page, which means more RAM for the same performance).

And cascading updates across several tables? Yikes. That means unnecessary I/O, bringing with it more row locks, index locks, gap locks, more potential cases for deadlocks...

No, I don't think the two apparent alternatives are likely to be genuinely balanced. Go with the surrogate key.

There are database purists out there who don't like surrogate keys, and if memory serves me, these may be the same people who spend too much time thinking about about theoretical databases and don't like NULL. Don't listen to those people too extensively, brilliant theoreticians they may be. (Dare I add, "caveat lector").

  • Sorry for the long wait but I tried to recreate an old example to be more concrete on why I am uneasy with extra join. I edited my question. Do I need to comment every answer to warn their authors? – Some_user Feb 17 '16 at 23:02
  • I believe a comment on the question itself may notify all of the answerers, but I am not certain. Your examples do show a significant performance decrease from the join, far more significant than should be expected. This needs investigation. SELECT @@innodb_buffer_pool_size; Also, index length 0 on the one table because the primary key index is where the row data is actually stored in InnoDB, so this would all be in data_length. – Michael - sqlbot Feb 17 '16 at 23:47
  • @@innodb_buffer_pool_size = '16777216' > it's wamp default value. – Some_user Feb 18 '16 at 0:58
  • Far too small. This is one of the very few tunable parameters that ever needs to be changed, but it's a very important one. Disk access is going to be killing your performance with only a 16MiB buffer pool. Configure it for half the machine's RAM, restart MySQL service and then check performance, remembering that the pool has to warm up, so the first queries will be the slowest after restart. – Michael - sqlbot Feb 18 '16 at 2:23
  • With innodb_buffer_pool_size=3000M I got 0s vs 0,42522s. Better but still not really convincing. >.> – Some_user Feb 20 '16 at 22:02
0

This is SQL but I suspect it holds up for mysql

A FK saves space and memory

Yes there is overhead to a join but if both sides are indexed then a small overhead

Consider a FK that is repeated a lot in the referencing table - like an audit table may repeat each user 1000+ times.

a1)

select count(*)
  from docSVsys 
 where docID < '3.683748.D303TUEPVBWI5LN1NTGROTWLN51VVRKBB'

b1)

 select count(*) 
   from docSVsys 
   join usr 
         on usr.ID = docSVsys.addBy 
        and usr.name < 'docAdmin'

a2)

select count(*) 
  from docSVsys 
 where docID like '%D3%'

b2)

 select count(*) 
   from docSVsys 
   join usr 
         on usr.ID = docSVsys.addBy 
        and usr.name like '%cA%'

In this case a1 is faster than b1 as the index seek is very fast and the overhead of the join makes a difference. But both are very fast.

But b2 is faster than a2 as once it is turned into an index scan the overhead of the join becomes a smaller relative factor. The join lets SQL do the hard stuff on a smaller set of data.

0

Do not mix types or collations when JOINing. That is, the column definition of the joined column should be virtually the same on both tables.

Do not fear JOINing on VARCHARs. Think of it this way... The effort to load a row is much bigger than to compare two strings.

Do have an index for the join column in one (or both) table. Otherwise, it will have to do a full table scan to find the match(es). This is sloooow.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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