Re: Benchmark shows very slow bulk delete

[Nikolas Everett <nik9000@xxxxxxxxx>]

On Wed, Jan 27, 2010 at 9:54 AM, Kevin Grittner <Kevin.Grittner@xxxxxxxxxxxx> 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.