List:General Discussion« Previous MessageNext Message »
From:Johan De Meersman Date:September 9 2010 2:28pm
Subject:Re: Does putting a LIMIT on a DELETE clause make any difference?
View as plain text  
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 <anandkl@stripped> 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 <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
> >
> >
>



-- 
Bier met grenadyn
Is als mosterd by den wyn
Sy die't drinkt, is eene kwezel
Hy die't drinkt, is ras een ezel

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