Correct. To verify this, simply create a select with the same structure as your delete - the execution plan will be similar.
I do not believe limit will help you, however, as it is only applied after execution, when the full dataset is known. On Thu, Sep 9, 2010 at 8:06 AM, Ananda Kumar <anan...@gmail.com> wrote: > 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 <dae...@daevid.com> 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 <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? > > > > > > > > > > > > -- > > MySQL General Mailing List > > For list archives: http://lists.mysql.com/mysql > > To unsubscribe: http://lists.mysql.com/mysql?unsub=anan...@gmail.com > > > > > -- Bier met grenadyn Is als mosterd by den wyn Sy die't drinkt, is eene kwezel Hy die't drinkt, is ras een ezel
