adding LIMIT 1 to improve performance?

"Aaron Saray" <102degrees@102degrees.com> wrote in message
news:1190347157.908675.66140@y42g2000hsy.googlegro ups.com...
> On Sep 20, 3:31 am, "Martijn Tonies" <m.ton...@upscene.removethis.com>
> wrote:
> > > > Before you do, what makes you think that adding LIMIT 1 to a
> > > > statement that searches the rows to modify by PRIMARY KEY!!
> > > > value would become any faster?
> >
> > > as I said, I noticed phpmyadmin does that, so I got this doubt and
> > > asked here (since it's not mentioned in the docs, AFAIK)
> >
> > It's probably just a standard thing PHPMyAdmin does for all table
> > updates, even the ones without a PK, to ensure only 1 row gets
> > updated.
> >
> As a force of habit, I tend to do limit x (usually 1) if I'm executing
> an update or a delete simply for the fact that I might accidentally
> create a sql statement that modifies more items than I wanted to by
> accident. Just a thought.

If your SQL statement affects more rows (meaning: your WHERE
statement is faulty) and you only want to modify 1, how do you know
which row is going to be affected if you use the LIMIT clause?


--
Martijn Tonies
Database Workbench - tool for InterBase, Firebird, MySQL, NexusDB, Oracle &
MS SQL Server
Upscene Productions
http://www.upscene.com
My thoughts:
http://blog.upscene.com/martijn/
Database development questions? Check the forum!
http://www.databasedevelopmentforum.com

On Sep 21, 12:59 am, "Martijn Tonies"
<m.ton...@upscene.removethis.com> wrote:
> "Aaron Saray" <102degr...@102degrees.com> wrote in message
>
> news:1190347157.908675.66140@y42g2000hsy.googlegro ups.com...
>
>
>
> > On Sep 20, 3:31 am, "Martijn Tonies" <m.ton...@upscene.removethis.com>
> > wrote:
> > > > > Before you do, what makes you think that adding LIMIT 1 to a
> > > > > statement that searches the rows to modify by PRIMARY KEY!!
> > > > > value would become any faster?
>
> > > > as I said, I noticed phpmyadmin does that, so I got this doubt and
> > > > asked here (since it's not mentioned in the docs, AFAIK)
>
> > > It's probably just a standard thing PHPMyAdmin does for all table
> > > updates, even the ones without a PK, to ensure only 1 row gets
> > > updated.
>
> > As a force of habit, I tend to do limit x (usually 1) if I'm executing
> > an update or a delete simply for the fact that I might accidentally
> > create a sql statement that modifies more items than I wanted to by
> > accident. Just a thought.
>
> If your SQL statement affects more rows (meaning: your WHERE
> statement is faulty) and you only want to modify 1, how do you know
> which row is going to be affected if you use the LIMIT clause?
>
> --
> Martijn Tonies
> Database Workbench - tool for InterBase, Firebird, MySQL, NexusDB, Oracle &
> MS SQL Server
> Upscene Productionshttp://www.upscene.com
> My thoughts:http://blog.upscene.com/martijn/
> Database development questions? Check the forum!http://www.databasedevelopmentforum.com

You don't - however, you should only write a sql statement that
affected one if you're expecting. However, I'm not perfect - I'm a
developer and I do mistakes! :) I'd rather butcher one row on a
faulty statement I wrote than delete many. (those with extremely
restrictive admins will recognize my frustrations - it may take days
to get a restore out of an admin).

> > > As a force of habit, I tend to do limit x (usually 1) if I'm executing
> > > an update or a delete simply for the fact that I might accidentally
> > > create a sql statement that modifies more items than I wanted to by
> > > accident. Just a thought.
> >
> > If your SQL statement affects more rows (meaning: your WHERE
> > statement is faulty) and you only want to modify 1, how do you know
> > which row is going to be affected if you use the LIMIT clause?
>
> You don't - however, you should only write a sql statement that
> affected one if you're expecting. However, I'm not perfect - I'm a
> developer and I do mistakes! :) I'd rather butcher one row on a
> faulty statement I wrote than delete many. (those with extremely
> restrictive admins will recognize my frustrations - it may take days
> to get a restore out of an admin).

This is why I like transactions :-)

"oh oh ... rollback" :-)

--
Martijn Tonies
Database Workbench - tool for InterBase, Firebird, MySQL, NexusDB, Oracle &
MS SQL Server
Upscene Productions
http://www.upscene.com
My thoughts:
http://blog.upscene.com/martijn/
Database development questions? Check the forum!
http://www.databasedevelopmentforum.com

On 20 Set, 10:31, "Martijn Tonies" <m.ton...@upscene.removethis.com>
wrote:
> It's probably just a standard thing PHPMyAdmin does for all table
> updates, even the ones without a PK, to ensure only 1 row gets
> updated.

you mean they use it on ALL such queries even if it's not needed, so
the php code to generate it is much simpler? Well, probably you're
right, thanks :)