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:[EMAIL PROTECTED] 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/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
