Fastest way to select on 0/1 flag

Hello!

I cannot figure out the fastest way to do a select on the floowing field:

f_spec tinyint not null;

It is a table of 100 000 records of products and f_spec is set only for
about 200 products.

I figure it could be done in two ways:

1) create an index on f_spec and do simple
select * from products where f_spec=1;

2) create a separate table

create table specs (
product_id int;
primary key (product_id)
);

then select ids from this table and join with the products table if needed.

What is the best way?

Also, it is often needed to know only the fact that there is any product
with f_spec set.
Is using index and doing
select id from products where f_spec=1 limit 1
will be very fast ?



Regards,
Artem

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=gcdmg-mysql-2@m.gmane.org

Re: Fastest way to select on 0/1 flag

Hi Artem,
There can be many malicious factors at play here, but if you are
not using an index then definitely create on now. It will obviously
help you with option 1 and you can still benefit from it with option
2. If you don't have an index, MySQL has to search for you data row by
row which is much slower than using an index.

2009/6/15 Artem Kuchin :
> Hello!
>
> I cannot figure out the fastest way to do a select on the floowing field:
>
> f_spec =A0 =A0tinyint not null;
>
> It is a table of 100 000 records of products and f_spec is set only for
> about 200 products.
>
> I figure it could be done in two ways:
>
> 1) create an index on f_spec and do simple
> select * from products where f_spec=3D1;
>
> 2) create a separate table
>
> create table specs (
> =A0 product_id =A0 int;
> =A0 primary key (product_id)
> );
>
> then select ids from this table and join with the products table if neede=
d.
>
> What is the best way?
>
> Also, it is often needed to know only the fact that there is any product
> with f_spec set.
> Is using index and doing
> select id from products where f_spec=3D1 limit 1
> will be very fast ?
>
>
>
> Regards,
> Artem
>
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe: =A0 =A0http://lists.mysql.com/mysql?unsub=3Ddsteplight@gm=
ail.com
>
>



--=20
A: It reverses the normal flow of conversation.
Q: What's wrong with top-posting?
A: Top-posting.
Q: What's the biggest scourge on plain text email discussions?

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=3Dgcdmg-mysql-2@m.gmane.o rg