Re: Indexing one byte flags - what implementattion is better
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) 10 products, 3 new products - interesting to know which method is better here and how each of the method performs. 3) 10 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]
Spawning Tables
Hi; I'm going to build a translation s/w and I'd like some advice. Many languages have compound words. I'd like to build a table that shows the component words. However, it depends on the compound word as to how many components it has! Now, I could take a safe guess and limit it to, say, 4 words. But then I'm wasting a lot of storage. So I thought I'd spawn a mini-table for each compound word. Is that the best way to handle this? TIA, Victor
Re: Spawning Tables
In my experience, having a limit of a particular number of anythings per something, eg. components per compound word is always a mistake, whether it's the number of payments allowed to pay an invoice, the number of children per parent, the number of cars per family, pets per owner, etc. There's always one that has one too many. And lots of them have only one or two. Go for the 'mini'-table. Jonathan Trahair I'm going to build a translation s/w and I'd like some advice. Many languages have compound words. I'd like to build a table that shows the component words. However, it depends on the compound word as to how many components it has! Now, I could take a safe guess and limit it to, say, 4 words. But then I'm wasting a lot of storage. So I thought I'd spawn a mini-table for each compound word. Is that the best way to handle this? TIA, Victor
copying of tbls...
Hi... I have a situation where I want to periodically copy new rows from a source database/tables on a remote server. Using a cron process, I'm looking to periodically copy the new tbl/rows into the dest database on my local server. i don't want to get into implementing slave/master replication, which would solve the issue. and while it's easy enough to simply copy all the tbls/rows, this would be overkill!! i'm looking for some soln that would allow me to copy thr latest newly inserted tbls/rows since i last copied. there's no timestamp field for the tbls, so i can't trigger off of time in the tbls/rows... any thoughts/comments on this... thanks -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
help with a query...
Hello, I'm trying to get what is for me quite a complicated query to work, if it's possible to do so anyway ... Here is my old query : SELECT a.message,a.date,b.surname,b.name,b.title FROM messages a JOIN info b ON a.from=b.code WHERE a.id='28' It worked fine untill I needed to have more than one 'code' in the info table ... To get data from the info table I do this : SELECT * FROM info WHERE code LIKE $code ORDER BY num DESC LIMIT 1 And the reason for this is I allow members to change their data, but I need to keep their old data. Because there are for example 3 lines with the same code in the messages table the messages are repeated 3 times. My question is, is there a way to limit the answer to once ? In otherwords this is what I want to do : SELECT a.message,a.date,b.surname,b.name,b.title FROM messages a JOIN info b ON a.from=(b.code ORDER BY b.num DESC LIMIT 1) WHERE a.id='28' Do you understand what I mean? I know it's not very clear ... :) Well here goes again just incase : I've got two tables. one called messages and the other called info. The messages table contains : id = autoincrement key from = members code who sent message date = date when message was sent For each message I need to get the firstname, surname and title which are stored in the info table. The info table contains : num = autoincrement key code = member code name = firstname surname = surname title = Sir, Miss or Mrs However each time a member changes his/her info it creates a new line instead of changing the old one. This is so I can keep track of what their old info was, a bit like the system a wiki uses. So for example I could have 3 lines with the member code 'm00025'. This is why I would need to be able to combine : SELECT a.message,a.date,b.surname,b.name,b.title FROM messages a JOIN info b ON a.from=b.code WHERE a.id='28' and ORDER BYnum DESC LIMIT 1 I hope I've been clear enough ... thanks in advance :) Richard -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Help with query, (question simplified as last mail was very complicated to understand :))
Sorry about my last email which was long and not clear. This is what I want to do Join two tables on code table1 = code table3 where messageid = for example 28 table 1 contains : message from messageid -- message1 | code1 |28 message2 | code1 |28 message3 | code1 |28 message4 | code1 |29 table 2 contains name | code | num -- name1 | code2 | 1 name2 | code1 | 2 name3 | code1 | 3 If I do : SELECT a.message,,b.name FROM table1 a JOIN table2 b ON a.code=b.code WHERE a.id='28' I get : message| name --- message1 | name2 message2 | name2 message3 | name2 message1 | name3 message2 | name3 message3 | name3 But all I want to get is : message| name --- message1 | name3 message2 | name3 message3 | name3 If I do : SELECT * FROM table2 WHERE code = 'code1' ORDER BY num DESC LIMIT 1 I get : name | code | num -- name3 | code1 | 3 I now need to somehow combine the two to get : message| name --- message1 | name3 message2 | name3 message3 | name3 Of course I have simplified everything down to the minimum :) Thanks in advance, Richard -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
