On Wed, Jan 27, 2010 at 9:54 AM, Kevin Grittner
<Kevin.Grittner@wicourts.gov> wrote:
It is if you don't have an index on the table which has a foreign
deletes. The author of the benchmark apparently didn't realize that
MySQL automatically adds such an index to the dependent table, while
PostgreSQL leaves it to you to decide whether to add such an index.
For "insert-only" tables, it isn't always worth the cost of
maintaining it.
It really gets to me that I have to not use some foreign keys in MySQL because I can't afford to maintain the index. I have to write super fun "check constraints" that look like
DELIMITER \\
CREATE TRIGGER Location_Pre_Delete BEFORE DELETE ON Locations FOR EACH ROW
BEGIN
DECLARE _id INT;
SELECT id INTO _id FROM BigHistoryTable WHERE locationId = OLD.id LIMIT 1;
IF _id IS NOT NULL THEN
INSERT INTO BigHistoryTable (column_that_does_not_exist_but_says_that_you_violated_my_hacked_foreign_key) VALUES ('fail');
END IF;
END\\
Sometimes I can't sleep at night for having written that code.