From: Daevid Vincent
Date: September 9 2010 12:17am
Subject: Does putting a LIMIT on a DELETE clause make any difference?
List-Archive: http://lists.mysql.com/mysql/222889
Message-Id: <44D141893AA34D2B8742450095CC432E@mascorp.com>
MIME-Version: 1.0
Content-Type: text/plain;
	charset="us-ascii"
Content-Transfer-Encoding: 7bit

I am curious about something.

I have a "glue" or "hanging" table like so:

CREATE TABLE `fault_impact_has_fault_system_impact` (
  `id_fault_impact` int(10) unsigned NOT NULL,
  `id_fault_system_impact` smallint(5) unsigned NOT NULL,
  KEY `fault_impact_key` (`id_fault_impact`,`id_fault_system_impact`),
  KEY `id_fault_system_impact` (`id_fault_system_impact`),
  CONSTRAINT `fault_impact_has_fault_system_impact_ibfk_1` FOREIGN KEY
(`id_fault_impact`) REFERENCES `fault_impact` (`id_fault_impact`) ON DELETE
CASCADE ON UPDATE CASCADE,
  CONSTRAINT `fault_impact_has_fault_system_impact_ibfk_2` FOREIGN KEY
(`id_fault_system_impact`) REFERENCES `fault_system_impact`
(`id_fault_system_impact`) ON DELETE CASCADE ON UPDATE CASCADE
) 

And a lookup table like this:

CREATE TABLE `fault_system_impact` (
  `id_fault_system_impact` smallint(5) unsigned NOT NULL auto_increment,
  `fault_sytem_impact_name` varchar(50) NOT NULL,
  PRIMARY KEY  (`id_fault_system_impact`)
) 

I have a bunch of checkboxes in a <FORM> and so in order to "update"
properly, I wipe out all the PK IDs and then start inserting. It looks like
this:

UPDATE `fault_impact` SET `bite_code` = '12', `bite_code_type` = '32',
`bite_subcode` = '21', `description_text` = 'Some random fault description
here.', `fault_id` = '11-11111', `fault_impact_other_explain` = '',
`id_fault_area_impact` = '3', `symptom_lru_id` = '232', `symptom_lru_subid`
= '34', `sys_perf_affected` = '', `update_date` = '2010-09-09 00:04:29'
WHERE id_fault_impact = '2495' LIMIT 1;

DELETE FROM fault_impact_has_fault_system_impact WHERE id_fault_impact =
2495;

INSERT INTO
fault_impact_has_fault_system_impact(`id_fault_impact`,`id_fault_system_imp
act`) VALUES(2495, 1);
INSERT INTO
fault_impact_has_fault_system_impact(`id_fault_impact`,`id_fault_system_imp
act`) VALUES(2495, 3);
INSERT INTO
fault_impact_has_fault_system_impact(`id_fault_impact`,`id_fault_system_imp
act`) VALUES(2495, 2);
INSERT INTO
fault_impact_has_fault_system_impact(`id_fault_impact`,`id_fault_system_imp
act`) VALUES(2495, 7);
INSERT INTO
fault_impact_has_fault_system_impact(`id_fault_impact`,`id_fault_system_imp
act`) VALUES(2495, 10);
INSERT INTO
fault_impact_has_fault_system_impact(`id_fault_impact`,`id_fault_system_imp
act`) VALUES(2495, 14);
INSERT INTO
fault_impact_has_fault_system_impact(`id_fault_impact`,`id_fault_system_imp
act`) VALUES(2495, 9);
INSERT INTO
fault_impact_has_fault_system_impact(`id_fault_impact`,`id_fault_system_imp
act`) VALUES(2495, 4);

Given that I know there can only be a maximum of id_fault_system_impact IDs
-- currently there are 17 rows in the fault_system_impact table -- and
they're unique to any given id_fault_impact, would it benefit me to change
my DELETE statement to something like this:

DELETE FROM fault_impact_has_fault_system_impact WHERE id_fault_impact =
2495 LIMIT 17;

Since the fault_impact_has_fault_system_impact table could have thousands
of rows and it seems that mySQL would do a table scan? Unfortunately, you
can't "EXPLAIN" on a DELETE to see what it might be doing. :( OR is mySQL
smart enough to know that the id_fault_impact is an index and therefore it
will just be right quick and stop after deleting those 8 rows above?