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 > >
