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:[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]
