Does putting a LIMIT on a DELETE clause make any difference?

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

Re: Does putting a LIMIT on a DELETE clause make any difference?

--001485f6d8928f706c048fcd6def
Content-Type: text/plain; charset=ISO-8859-1

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 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 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_f ault_system_imp
> act`) VALUES(2495, 1);
> INSERT INTO
> fault_impact_has_fault_system_impact(`id_fault_impact`,`id_f ault_system_imp
> act`) VALUES(2495, 3);
> INSERT INTO
> fault_impact_has_fault_system_impact(`id_fault_impact`,`id_f ault_system_imp
> act`) VALUES(2495, 2);
> INSERT INTO
> fault_impact_has_fault_system_impact(`id_fault_impact`,`id_f ault_system_imp
> act`) VALUES(2495, 7);
> INSERT INTO
> fault_impact_has_fault_system_impact(`id_fault_impact`,`id_f ault_system_imp
> act`) VALUES(2495, 10);
> INSERT INTO
> fault_impact_has_fault_system_impact(`id_fault_impact`,`id_f ault_system_imp
> act`) VALUES(2495, 14);
> INSERT INTO
> fault_impact_has_fault_system_impact(`id_fault_impact`,`id_f ault_system_imp
> act`) VALUES(2495, 9);
> INSERT INTO
> fault_impact_has_fault_system_impact(`id_fault_impact`,`id_f ault_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=anandkl@gmail.com
>
>

--001485f6d8928f706c048fcd6def--

Re: Does putting a LIMIT on a DELETE clause make any difference?

--000e0cd6a95c30aeba048fd47184
Content-Type: text/plain; charset=ISO-8859-1

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 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 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 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_f ault_system_imp
> > act`) VALUES(2495, 1);
> > INSERT INTO
> >
> fault_impact_has_fault_system_impact(`id_fault_impact`,`id_f ault_system_imp
> > act`) VALUES(2495, 3);
> > INSERT INTO
> >
> fault_impact_has_fault_system_impact(`id_fault_impact`,`id_f ault_system_imp
> > act`) VALUES(2495, 2);
> > INSERT INTO
> >
> fault_impact_has_fault_system_impact(`id_fault_impact`,`id_f ault_system_imp
> > act`) VALUES(2495, 7);
> > INSERT INTO
> >
> fault_impact_has_fault_system_impact(`id_fault_impact`,`id_f ault_system_imp
> > act`) VALUES(2495, 10);
> > INSERT INTO
> >
> fault_impact_has_fault_system_impact(`id_fault_impact`,`id_f ault_system_imp
> > act`) VALUES(2495, 14);
> > INSERT INTO
> >
> fault_impact_has_fault_system_impact(`id_fault_impact`,`id_f ault_system_imp
> > act`) VALUES(2495, 9);
> > INSERT INTO
> >
> fault_impact_has_fault_system_impact(`id_fault_impact`,`id_f ault_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=anandkl@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

--000e0cd6a95c30aeba048fd47184--