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.

"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