Maybe someone could provide a good resonable
input on this issue.

Let's say i have a table products

CASE 1:

table: products

id int unsigned not null,
name char(128) not null,
f_new tinyint not null

id - is basically the id of a product
name - is the name of a product
f_new - is a one byte flag. If it is 1 the product is condireed new.


In this case to select all new products including name i need to do:

select id, name from products wher f_new=1

CASE 2:

The above can be done another way - via two table,
one products table and another one - listing all ids
for new products

create table products (
id int unsigned not null,
name char(128) not null,
primay key (id)
);

create table newproducts (
product_id int unsigned not null,
primay key (id)
);


If product is is in newproducts table that it is
a new product.

To choose all new products including name i need to do:

SELECT id,name
FROM newproducts
INNER JOIN products ON products.id=newproducts.product_id

The questions are:

1) which way is FASTER?
2) which way eats less memory?
3) which way eats less cpu?
4) which way eats less hdd io?

There are several cases for each question:
1) <1000 products - i think both methods are pretty much the same
in this case because all of the data woul be cached in memory

2) 100000 products, 30000 new products - interesting to know
which method is better here and how each of the method performs.

3) 100000 products, 50 new products - interesting to know
which method is better here and how each of the method performs.


I will greately appriciate input on this issue.

--
Artem

Is id a sequential number? And is it referenced by other tables? If so,
and if over time new products become "old" products, then CASE 2 is more
complex, because when moving a product (i.e., a row) from the new
product table to the old product table, the value of id needs to stay
the same. So for CASE 2 you'll need a third object to keep track of the
highest value for id.

-----Original Message-----
From: Artem Kuchin [mailto:matrix@itlegion.ru]
Sent: Thursday, December 27, 2007 1:19 PM
To: mysql@lists.mysql.com
Subject: Indexing one byte flags - what implementattion is better

Maybe someone could provide a good resonable
input on this issue.

Let's say i have a table products

CASE 1:

table: products

id int unsigned not null,
name char(128) not null,
f_new tinyint not null

id - is basically the id of a product
name - is the name of a product
f_new - is a one byte flag. If it is 1 the product is condireed new.


In this case to select all new products including name i need to do:

select id, name from products wher f_new=1

CASE 2:

The above can be done another way - via two table,
one products table and another one - listing all ids
for new products

create table products (
id int unsigned not null,
name char(128) not null,
primay key (id)
);

create table newproducts (
product_id int unsigned not null,
primay key (id)
);


If product is is in newproducts table that it is
a new product.

To choose all new products including name i need to do:

SELECT id,name
FROM newproducts
INNER JOIN products ON products.id=newproducts.product_id

The questions are:

1) which way is FASTER?
2) which way eats less memory?
3) which way eats less cpu?
4) which way eats less hdd io?

There are several cases for each question:
1) <1000 products - i think both methods are pretty much the same
in this case because all of the data woul be cached in memory

2) 100000 products, 30000 new products - interesting to know
which method is better here and how each of the method performs.

3) 100000 products, 50 new products - interesting to know
which method is better here and how each of the method performs.


I will greately appriciate input on this issue.

--
Artem


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:
http://lists.mysql.com/mysql?unsub=n...ris@dof.ca.gov

Garris, Nicole wrote:
> Is id a sequential number? And is it referenced by other tables? If
> so, and if over time new products become "old" products, then CASE 2
> is more complex, because when moving a product (i.e., a row) from the
> new product table to the old product table, the value of id needs to
> stay the same. So for CASE 2 you'll need a third object to keep track
> of the highest value for id.

I think you did not get it right. There is no MOVING of products.
All product are stored in 'products' table only, and newproducts
is just is kind of FLAG table. It contains ONLY ids of products
considered new, nothing else. So, if product is new then its is
of course in products table and in newproducts table and if it is not new
then it is only in 'products' table.

ID is a seqential number (but no auto_increment - i hate it).

PS: top posting is really popular in mysql list!

--
Artem


> -----Original Message-----
> From: Artem Kuchin [mailto:matrix@itlegion.ru]
> Sent: Thursday, December 27, 2007 1:19 PM
> To: mysql@lists.mysql.com
> Subject: Indexing one byte flags - what implementattion is better
>
> Maybe someone could provide a good resonable
> input on this issue.
>
> Let's say i have a table products
>
> CASE 1:
>
> table: products
>
> id int unsigned not null,
> name char(128) not null,
> f_new tinyint not null
>
> id - is basically the id of a product
> name - is the name of a product
> f_new - is a one byte flag. If it is 1 the product is condireed new.
>
>
> In this case to select all new products including name i need to do:
>
> select id, name from products wher f_new=1
>
> CASE 2:
>
> The above can be done another way - via two table,
> one products table and another one - listing all ids
> for new products
>
> create table products (
> id int unsigned not null,
> name char(128) not null,
> primay key (id)
> );
>
> create table newproducts (
> product_id int unsigned not null,
> primay key (id)
> );
>
>
> If product is is in newproducts table that it is
> a new product.
>
> To choose all new products including name i need to do:
>
> SELECT id,name
> FROM newproducts
> INNER JOIN products ON products.id=newproducts.product_id
>
> The questions are:
>
> 1) which way is FASTER?
> 2) which way eats less memory?
> 3) which way eats less cpu?
> 4) which way eats less hdd io?
>
> There are several cases for each question:
> 1) <1000 products - i think both methods are pretty much the same
> in this case because all of the data woul be cached in memory
>
> 2) 100000 products, 30000 new products - interesting to know
> which method is better here and how each of the method performs.
>
> 3) 100000 products, 50 new products - interesting to know
> which method is better here and how each of the method performs.
>
>
> I will greately appriciate input on this issue.
>
> --
> Artem
>
>
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe:
> http://lists.mysql.com/mysql?unsub=n...ris@dof.ca.gov