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=arch...@jab.org
