List:General Discussion« Previous MessageNext Message »
From:Ananda Kumar Date:September 9 2010 6:06am
Subject:Re: Does putting a LIMIT on a DELETE clause make any difference?
View as plain text  
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 <daevid@stripped> 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=1
>
>

Thread
Does putting a LIMIT on a DELETE clause make any difference?Daevid Vincent9 Sep
  • Re: Does putting a LIMIT on a DELETE clause make any difference?Ananda Kumar9 Sep
    • Re: Does putting a LIMIT on a DELETE clause make any difference?Johan De Meersman9 Sep
  • Re: Does putting a LIMIT on a DELETE clause make any difference?Ananda Kumar9 Sep