MySQL - are FK's useful / viable in a web app?

Question

Hi all,

I've encountered this discussion related to FK's and web applications. Basically some people say that FK's in web applications doesn't represent a real improvement and can even make the application slower in some cases.

What do you guys think, what's your experience?

edit : note that I'm aware of FK's job and objective, I'm just not sure if they would have a significant negative impact on the performance of a web application like youtube or something similar.

--

A quote from Heikki Tuuri, creator of InnoDB engine, founder and CEO of Innobase:

InnoDB checks foreign keys as soon as a row is updated, no batching is performed or checks delayed till transaction commit Foreign keys are often serious performance overhead, but help maintain data consistency

Foreign Keys increase amount of row level locking done and can make it spread to a lot of tables besides the ones directly updated

Answer 1

Keep your foreign keys - it ensures, no matter what muppet you have writing code for you, that your data stays in a usable and expected format. Unless you're looking at seriously high volumes of traffic, I wouldn't even consider removing them.

Answer 2

Foreign keys will guarantee that a row in a table order_details with a field order_id referencing an orders table will never have an order_id value that doesn't exist in the orders table.

Foreign keys aren't required to have a working relational database, but they are definitely essential to avoid broken relationships and orphan rows (ie. referential integrity). The ability to enforce referential integrity at the database level is required for the C in ACID to stand.

As for your concerns regarding performance, in general the performance hit, if any, will be negligible. I suggest putting in all your foreign key constraints, and only experiment without them if you have real performance issues that you cannot solve otherwise.

In addition, as a side-note, while not directly related to MySQL, this is quote from Microsoft Patterns and Practices: Chapter 14 Improving SQL Server Performance:

When primary and foreign keys are defined as constraints in the database schema, the server can use that information to create optimal execution plans.

Answer 3

Web applications are no different from any other kind of application. Sure, you may be able to get a marginal speedup from omitting data integrity checks, but it's not going to be a lot, and the price is too high.

Without referential integrity, as soon as you get a dangling foreign key (because of some improperly-tested code path or a failed partial update*) a lot of your queries, that were relying on that key pointing to something, will break. This very commonly makes your whole web site fall over until you delve into the database and delete the dangling reference manually.

(*: unless you're using transactions. But let's face it, if you're the kind of author who jerry-rigs web applications without referential integrity, you're hardly going to be using transactions either.)

Answer 4

In the future, you may have some other piece of software manipulating data in your application's database. For example:

• Bulk data imports from a 3rd party system
• Batch processing that is to heavy to do in real time
• etc

By using the foreign keys, you will ensure that the integrity of your database is maintained as any operations that violate the keys will result in an error.

IMHO, the performance gain (if any??) is not worth the potential risk of corrupting the integrity of your database.

Answer 5

Foreign keys are all about integrity and functionality, not performance. If you wish to enforce referential integrity between two sets of columns then a foreign key is typically the most efficient and effective way to do it. If you don't need to support such an integrity constraint then obviously the performance question doesn't arise anyway.

It is possible that a foreign key could improve performance by supporting certain types of query rewrite that wouldn't otherwise be possible. Howevever that's mainly a fringe benefit - a side effect of ensuring that the constraint is being enforced.

Answer 6

Foreign Keys are a useful barrier between you and data integrity issues. They tell the database to do extra work on your behalf to make SURE that the integrity you define is enforced, no matter how badly some application code screws up. This has saved every DB programmer's neck at least once in their career. You should absolutely design foreign keys into your model, especially if you think you don't need them.

That said, if you're pushing the limits of scalability on your database tier, FKs can be something you turn off, because in theory, they're doing duplicate work that your app is already doing (that is, your app shouldn't be coded to intentionally do things that would break relational integrity, if not for the FKs). This isn't for the faint of heart, and for that matter, it isn't ALWAYS a performance win (some JOINs are more efficient because the DB knows whether certain relationships can or cannot have resulting rows).

As in all things optimization, rule 1 is "Don't do it" and rule 2 is "Don't do it (yet)". Make sure that if you're going down this path:

1) you have a specific performance load data to back up your decision

2) you have the ability to turn them on or off with a switch (i.e. don't delete them and throw away the code, and definitely don't just neglect to design them in the first place)

3) you have some ability to periodically verify that the integrity constraints you designed are being properly enforced by the application code (for example, a nightly job that verifies there are no orphans)

Answer 7

Your question is like arguing that race cars don't need steering wheels because it slows them down - nonsensical. All cars need a steering wheel (at least the cars that are built today). Others have already explained in detail why foreign keys are useful for data integrity. In addition, I would like to point out that modern languages and frameworks can use the foreign key metadata in the database to establish object relationships. Linq-to-SQL does this very elegantly. You cannot really work with an Object-relational mapping framework without defining foreign keys whenever data relationships mandate them.

Answer 8

Apart from all the good reasons mentioned here (actually there is no reason not using them), there is another good one:

Foreign keys document your data model and makes it easier for humans to understand it (this is similar to the advantage that cdonner mentioned)