I occasionally get in to light arguments healthy discussions with students about whether or not to use Foreign Key constraints on InnoDB tables. My standard response has always been: “it depends on how much of a tradeoff you are willing to make for performance. In some situations the cost can be considerable”.
.. that’s when they expect me to “come up with some real proof” to show them. I do not disagree with their logic or proof being on their list-of-demands. I support the use of data to make decisions.
The problem is that MySQL has (traditionally) been lacking the instrumentation required to make these decisions easy. This is getting better – here is an example we recently added to our InnoDB course:
CREATE TABLE parent ( id INT NOT …[Read more]