1

This question is for the experienced developers out there.

My friend recently told me that his employer has a legacy code base whose MySQL database has now grown to around 800 GB in size. What's interesting is that there are very few foreign key constraints, resulting in some duplicated bad data, but since that doesn't harm anything (apparently), nobody is overly concerned.

Now I can't imagine life without foreign keys, so when I asked him why there were no such checks, he replied by saying that a foreign key is another index that the database has to maintain, and takes more space.

Yes, space. So here's my question: Just how much more space can an index take? 10% of the DB? 20%? Even if his reasoning was wrong, there's no denying that foreign keys entail a performance penalty. Would you, as a developer, let go of foreign keys and instead rely on code to do proper housekeeping once the DB approached 1 TB in size?

6
  • 3
    What is a performance penalty is having inconsistent data! The performance being the company being open for business or closed.
    – Ed Heal
    Aug 8 '15 at 11:21
  • 2
    Ow that hurts reading. I can't offer statistics on size occupied by Foreign Keys but I am very certain they do not give a performance penalty. Quite the opposite doing something like a join without foreign keys will be terrible for performance. You will also have more values to check if you have duplicates.
    – John
    Aug 8 '15 at 11:46
  • 2
    I've seen indexes on a poorly designed (very large) table take as much space as the table itself.
    – user25999
    Aug 8 '15 at 13:37
  • 1
    In one database I work with (now 3TiB), foreign keys became an issue because they slowed down inserts before their size became an issue.
    – TehShrike
    Aug 8 '15 at 15:37
  • 2
    I've also seen well designed databases where the indexes take more space than the base tables themselves. That's quite often. Aug 8 '15 at 15:38
11

...a foreign key is another index that the database has to maintain

I think your colleague may be laboring under the idea that columns containing foreign keys must be indexed, which isn't always the case. (See ypercube's comment below.) Foreign keys aren't anything other than values that the schema gives a special meaning. The decision to index such a column is the same as for any other: if it will frequently be the subject of selection criteria (i.e., part of a WHERE clause), index it according your DBMS's preferred practices.

Indexes come into play in the parent table's primary key column because those columns meet the criteria described above. More on that in a minute.

...and takes more space.

Almost everything in this field is a space/time trade-off, and indexes are one of them: you consume more storage during writing to consume less time during reading. It would be very hard to argue that adding an index to a table doesn't consume additional space, because it does. Making those kinds of decisions without the context of the whole schema is failing to see the forest for the trees.

As Codd described it, normalization was a very early application of what would become the DRY principle. Non-normalized data is inherently repetitive; normalized data is not. References in child tables are (necessarily) repetitive but have the benefit of being smaller than the original. (Normalization is more for preserving integrity rather than space, but that's another discussion.)

For example, a million copies of the string 1600 Pennsylvania Avenue stored in a million rows of a non-normalized table occupy 25 MB. Move that string to a separate table of addresses (where it's stored exactly once), refer to it a million times using an eight-byte, numeric foreign key and the whole mess occupies 8 MB plus 25 bytes for the actual string. That's space savings of about 67%.

This foreign-key relationship may work better performance-wise if the table of addresses (parent) is indexed by its primary key. That index is a lot smaller because the N:1 nature of normalization means that, on average, there will be fewer rows to index and the index won't need updating as often.

It's hard to say without seeing the schema, but a lack of normalization may be the reason the database grew to 800 GB in the first place.

Just how much more space can an index take?

That's a hard question to answer in general terms. The idea behind most forms of indexing is to allow the traversal of a small amount of data to locate something in a large corpus. A side effect is that the data structures used to represent it tend to end up much smaller. That said, a poorly-designed or poorly-selected mechanism combined with pathologically-bad data can lead to indexes that are much larger than the original data. The opposite, more-usual case is that the index is a small fraction. I've done a lot of work with full-text indexers and find they tend to chew up less than 5% of the original data. But that's just for my use cases and may not apply to anyone else's.

4
  • 3
    Your first paragraph is correct in general. But MySQL (InnoDB) implementation of foreign keys requires them (both the referenced and the referencing keys) being indexed. Aug 8 '15 at 15:40
  • 2
    @ypercube: This question was migrated from Programmers, where a more-general answer is more appropriate. There are good reasons to index FK columns, but I can't think of a good reason why InnoDB should need to require it unless the default delete behavior includes cascading.
    – Blrfl
    Aug 8 '15 at 16:22
  • 2
    I like your answer. I only commented because the question is tagged with [mysql]. (the default is RESTRICT for both ON DELETE and ON UPDATE.) A general answer is good here, too. Aug 8 '15 at 16:38
  • 1
    @Blrfl You are quite right! There indeed is nontrivial duplication of data because of lack of foreign keys, although I believe it won't be more than 20-30%. But all that aside, I think you're making the point that if the schema is well-designed, indexes are small in size; which means that my friend has got the foundations of the database wrong.
    – ankush981
    Aug 11 '15 at 10:07
1

Life without FOREIGN KEYs...

  • Since an FK creates an index, getting rid of an FK might get rid of an index -- or you might need that index for other reasons. It's the INDEX, not the FK that makes JOINs efficient. Without FKs, you focus on getting the optimal indexes, often composite indexes, which are unlikely to come from FKs.

  • Since an FK implies a check when writing to the table, you might save those checks. Actually, I would say you are probably saving a check, because you probably would not bother to, or need to, check. Most likely, you have code that assures that the check is actually redundant, in which case the check is a waste. (At 800GB, these checks may be the biggest "cost" of FKs.)

  • With 800GB, you are not doing any manual changes to the table; everything is 'automated'. This implies that you have done some serious code checking before going into production. Part of those checks it to assure yourself that no FK violations (explicit or implicit) are happening. Correct?

  • Normalization is a common use for FKs. Your automated code to augment normalization tables is tuned to insert just the necessary rows and create no duplicates, correct? Hence FKs don't do anything other than slow the process down.

2
  • 2
    You can't be serious. I'm withholding my downvote on this, in the hopes that you are actually using reverse psychology to convince OP that not using foreign key constraints for alleged performance reasons is preposterous. Aug 11 '15 at 2:56
  • @Michael-sqlbot - With 1TB, performance and space are likely to be a concerns. Let's just say I and playing devil's advocate. (And it sounds like I was persuasive enough to get you riled up.)
    – Rick James
    Aug 15 '15 at 23:38

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.