is limit 1 needed on a query on and auto_increment or unique column

Say I have a simple table with an ordinary auto_increment column called
id.
Is MySQL smart enough to know that a query like

select * from mytab where id=123

will have only one match, or is it always a good idea to append limit 1
to such queries? If mysql knows (and my guess is that it probably
does),
then does adding limit 1 actually do anything to slow down the query?

The same question would apply to a table with a column defined with
the UNIQUE constraint.

lynn.newton@gmail.com wrote:
> Say I have a simple table with an ordinary auto_increment column called
> id.
> Is MySQL smart enough to know that a query like
>
> select * from mytab where id=123
>
> will have only one match, or is it always a good idea to append limit 1
> to such queries? If mysql knows (and my guess is that it probably
> does),
> then does adding limit 1 actually do anything to slow down the query?
>
> The same question would apply to a table with a column defined with
> the UNIQUE constraint.
>

Unless this column is the table's primary key or has a unique index,
even an autoincrement column can have duplicates.

Autoincrement is only the default. You can still specify your own
values on an insert or update statement.

--
==================
Remove the "x" from my email address
Jerry Stuckle
JDS Computer Training Corp.
jstucklex@attglobal.net
==================

> Say I have a simple table with an ordinary
> auto_increment column called id. Is MySQL smart
> enough to know that a query like

> select * from mytab where id=123

> will have only one match, or is it always a good
> idea to append limit 1 to such queries? If mysql
> knows (and my guess is that it probably does), then
> does adding limit 1 actually do anything to slow
> down the query?

> The same question would apply to a table with a
> column defined with the UNIQUE constraint.

> Unless this column is the table's primary key or has a unique index,
> even an autoincrement column can have duplicates.

> Autoincrement is only the default. You can still specify your own
> values on an insert or update statement.

You are right. But suppose it *is* a primary key or has
a unique index, is adding "limit 1" superfluous, i.e.,
does MySQL know to stop searching for matches after
finding one because it knows by the table definition
that there can't be any more?

lynn.newton@gmail.com wrote:
> > Say I have a simple table with an ordinary
> > auto_increment column called id. Is MySQL smart
> > enough to know that a query like
>
> > select * from mytab where id=123
>
> > will have only one match, or is it always a good
> > idea to append limit 1 to such queries? If mysql
> > knows (and my guess is that it probably does), then
> > does adding limit 1 actually do anything to slow
> > down the query?
>
> > The same question would apply to a table with a
> > column defined with the UNIQUE constraint.
>
>
>>Unless this column is the table's primary key or has a unique index,
>>even an autoincrement column can have duplicates.
>
>
>>Autoincrement is only the default. You can still specify your own
>>values on an insert or update statement.
>
>
> You are right. But suppose it *is* a primary key or has
> a unique index, is adding "limit 1" superfluous, i.e.,
> does MySQL know to stop searching for matches after
> finding one because it knows by the table definition
> that there can't be any more?
>

It will stop searching for matches because there is only one in the
primary (or unique) index. It won't find any more.

--
==================
Remove the "x" from my email address
Jerry Stuckle
JDS Computer Training Corp.
jstucklex@attglobal.net
==================