From: Garris, Nicole (Nicole.Garrisdof.ca.gov)
Date: Thu Dec 27 2007 - 15:26:47 CST

• Messages sorted by: [ date ] [ thread ] [ subject ] [ author ]

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:matrixitlegion.ru]
Sent: Thursday, December 27, 2007 1:19 PM
To: mysqllists.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=nicole.garrisdof.ca.gov

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql

• Messages sorted by: [ date ] [ thread ] [ subject ] [ author ]