Re: Benchmark shows very slow bulk delete

On Wed, Jan 27, 2010 at 9:54 AM, Kevin Grittner <Kevin(dot)Grittner(at)wicourts(dot)gov
> wrote:

> It is if you don't have an index on the table which has a foreign
> key defined which references the table in which you're doing
> 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.