adding LIMIT 1 to improve performance?

If I have a query that WILL affect only one row (or none), because it
has a condition such as " WHERE primary_key='$x' ", adding "LIMIT 1"
will improve performance, or mysql is smart enough anyway?

I ask this because I noticed phpmyadmin often does that in situations
like this, so this doubt hit me.

Thanks (hope I explained clearly enough).

Lo'oris wrote:
> If I have a query that WILL affect only one row (or none), because it
> has a condition such as " WHERE primary_key='$x' ", adding "LIMIT 1"
> will improve performance, or mysql is smart enough anyway?
>
> I ask this because I noticed phpmyadmin often does that in situations
> like this, so this doubt hit me.
>
> Thanks (hope I explained clearly enough).
>
Here is the official version with some user comments:
http://dev.mysql.com/doc/refman/5.0/...imization.html

--

//Aho

It does not jack shit for performance.

It does a big select, and then gives you the first item.

Yes, I did try it.

i

On Sat, 15 Sep 2007 14:02:40 -0000, Lo'oris <looris@gmail.com> wrote:
> If I have a query that WILL affect only one row (or none), because it
> has a condition such as " WHERE primary_key='$x' ", adding "LIMIT 1"
> will improve performance, or mysql is smart enough anyway?
>
> I ask this because I noticed phpmyadmin often does that in situations
> like this, so this doubt hit me.
>
> Thanks (hope I explained clearly enough).
>

On 15 Set, 16:17, "J.O. Aho" <u...@example.net> wrote:
> Lo'oris wrote:
> > has a condition such as " WHERE primary_key='$x' ", adding "LIMIT 1"
>
> Here is the official version with some user comments:http://dev.mysql.com/doc/refman/5.0/...imization.html

but it does not talk about primary keys :/

Lo'oris wrote:

> On 15 Set, 16:17, "J.O. Aho" <u...@example.net> wrote:
>> Lo'oris wrote:
>> > has a condition such as " WHERE primary_key='$x' ", adding "LIMIT 1"
>>
>> Here is the official version with some user
>> comments:http://dev.mysql.com/doc/refman/5.0/...imization.html
>
> but it does not talk about primary keys :/


Why don't you write a script to benchmark it? Do it with and without LIMIT
1 a few hundred thousand times each, repeat the test half-a-dozen times and
post your results.

Make sure you turn off query cache first if the queries are selects.


--
Brian Wakem

On 16 Set, 20:10, Brian Wakem <n...@email.com> wrote:
> Why don't you write a script to benchmark it? Do it with and without LIMIT
> 1 a few hundred thousand times each, repeat the test half-a-dozen times and
> post your results.
>
> Make sure you turn off query cache first if the queries are selects.

great idea!! I'll let you know (but not soon, I'll do that in later
stages in this case :)

> On 16 Set, 20:10, Brian Wakem <n...@email.com> wrote:
> > Why don't you write a script to benchmark it? Do it with and without
LIMIT
> > 1 a few hundred thousand times each, repeat the test half-a-dozen times
and
> > post your results.
> >
> > Make sure you turn off query cache first if the queries are selects.
>
> great idea!! I'll let you know (but not soon, I'll do that in later
> stages in this case :)

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?


--
Martijn Tonies
Database Workbench - development tool for MySQL, and more!
Upscene Productions
http://www.upscene.com
My thoughts:
http://blog.upscene.com/martijn/
Database development questions? Check the forum!
http://www.databasedevelopmentforum.com

> 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)

> > 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.


--
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 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.
>
> --
> 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

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.