From: Ananda Kumar Date: September 9 2010 6:06am Subject: Re: Does putting a LIMIT on a DELETE clause make any difference? List-Archive: http://lists.mysql.com/mysql/222891 Message-Id: MIME-Version: 1.0 Content-Type: multipart/alternative; boundary=001485f6d8928f706c048fcd6def --001485f6d8928f706c048fcd6def Content-Type: text/plain; charset=ISO-8859-1 Vincent, Since the column is indexed, it would use the index during the delete. regards anandkl On Thu, Sep 9, 2010 at 5:47 AM, Daevid Vincent wrote: > 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
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? > > > > > > -- > MySQL General Mailing List > For list archives: http://lists.mysql.com/mysql > To unsubscribe: http://lists.mysql.com/mysql?unsub=anandkl@stripped > > --001485f6d8928f706c048fcd6def--