select * performance question

For a moderately busy website (few hundred queries per second), would
there be noticeable performance difference between using:

select * from product where id = 5

vs

select * from product where name = 'baseball'

thanks for any info.

happy holidays,
chad

Chad wrote:
> For a moderately busy website (few hundred queries per second), would
> there be noticeable performance difference between using:
>
> select * from product where id = 5
>
> vs
>
> select * from product where name = 'baseball'
>
> thanks for any info.
>
> happy holidays,
> chad

That'd be pretty easy to test for yourself! My guess is that there
wouldn't be much difference - and probably less than the difference
saying "SELECT *..." and "SELECT field1,field2,filed3,etc..."

Hi,

You'd want to make sure the column you're selecting on is properly
indexed. In your example, your table might be defined like so:

create table t (
id int primary key auto_increment
name varchar(255) unique key
);

Then you could lookup records by id or by name, and have an index in
either case. You then wouldn't see much difference in query speed in
your app.

Some more info:

http://www.databasejournal.com/featu...le.php/1382791

Chad wrote:
> For a moderately busy website (few hundred queries per second), would
> there be noticeable performance difference between using:
>
> select * from product where id = 5
>
> vs
>
> select * from product where name = 'baseball'
>
> thanks for any info.
>
> happy holidays,
> chad

> For a moderately busy website (few hundred queries per second), would
> there be noticeable performance difference between using:
>
> select * from product where id = 5
>
> vs
>
> select * from product where name = 'baseball'

You can test that using the BENCHMARK() function:
http://dev.mysql.com/doc/refman/4.1/...functions.html
The test expression can also be a query.

--
Willem Bogaerts

Application smith
Kratz B.V.
http://www.kratz.nl/

Chad wrote:
> For a moderately busy website (few hundred queries per second), would
> there be noticeable performance difference between using:
>
> select * from product where id = 5
>
> vs
>
> select * from product where name = 'baseball'
>
> thanks for any info.
>
> happy holidays,
> chad
>

It would depend several factors, including the size of the table and
length of the string being compared.

In general, an integer comparison will always be the fastest. String
comparisons take longer. A single comparison of two short strings won't
take significantly longer, but as the string gets longer and the number
of rows increases (requiring more comparisons), the difference will grow.

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