Re: Best way to combine MYISAM to MERGE tables ...
- Original Message - From: "mos" <[EMAIL PROTECTED]> To: Sent: Monday, February 18, 2008 6:48 PM Subject: Re: Best way to combine MYISAM to MERGE tables ... At 11:33 AM 2/18/2008, you wrote: Hi All, I am working with MYISAM tables split by year, like: data2003, data2004, data2005, data2006, data2007, data2008, all having the same definitions. To speed up the query process, I also defined MERGE tables, like: CREATE TABLE data20032004 ... ENGINE=MERGE UNION (data2003, data2004); CREATE TABLE data20032005 ... ENGINE=MERGE UNION (data2003, data2004, data2005); CREATE TABLE data20032006 ... ENGINE=MERGE UNION (data2003, data2004, data2005, data2006); CREATE TABLE data20032007 ... ENGINE=MERGE UNION (data2003, data2004, data2005, data2006, data2007); CREATE TABLE data20032008 ... ENGINE=MERGE UNION (data2003, data2004, data2005, data2006, data2007, data2008); CREATE TABLE data20042005 ... ENGINE=MERGE UNION (data2004, data2005); etc. etc. CREATE TABLE data20072008 ... ENGINE=MERGE UNION (data2007, data2008); For example, if a user query applies to 2003..2006, my app selects the belonging MERGE table data20032006, excluding tables data2007 and data2008 for faster results. Is this assumption correct ? Or are there better ways of doing this ? I'm using: mySQL version 5.0.15-NT TIA, Cor Cor, If the date is indexed, I don't think you're going to have a problem referencing a merge table "Data_All" based on all the tables. That's what I do with 25 tables (15 million rows) and it is quite fast. It also of course uses the query cache so subsequent queries are instant. Mike Thanks Mike, In my app I can't to use Year as Indexed field, because my app has multi-column keys with (5) higher selectivity fields. Key on Year would be an option, if MySQL search engine could use N separate keys. By the way, I'm using about 120 million rows in 10 tables. I will keep your experience in mind, thanks. Regards, Cor -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Best way to combine MYISAM to MERGE tables ...
At 11:33 AM 2/18/2008, you wrote: Hi All, I am working with MYISAM tables split by year, like: data2003, data2004, data2005, data2006, data2007, data2008, all having the same definitions. To speed up the query process, I also defined MERGE tables, like: CREATE TABLE data20032004 ... ENGINE=MERGE UNION (data2003, data2004); CREATE TABLE data20032005 ... ENGINE=MERGE UNION (data2003, data2004, data2005); CREATE TABLE data20032006 ... ENGINE=MERGE UNION (data2003, data2004, data2005, data2006); CREATE TABLE data20032007 ... ENGINE=MERGE UNION (data2003, data2004, data2005, data2006, data2007); CREATE TABLE data20032008 ... ENGINE=MERGE UNION (data2003, data2004, data2005, data2006, data2007, data2008); CREATE TABLE data20042005 ... ENGINE=MERGE UNION (data2004, data2005); etc. etc. CREATE TABLE data20072008 ... ENGINE=MERGE UNION (data2007, data2008); For example, if a user query applies to 2003..2006, my app selects the belonging MERGE table data20032006, excluding tables data2007 and data2008 for faster results. Is this assumption correct ? Or are there better ways of doing this ? I'm using: mySQL version 5.0.15-NT TIA, Cor Cor, If the date is indexed, I don't think you're going to have a problem referencing a merge table "Data_All" based on all the tables. That's what I do with 25 tables (15 million rows) and it is quite fast. It also of course uses the query cache so subsequent queries are instant. Mike -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Best way to combine MYISAM to MERGE tables ...
Hi All, I am working with MYISAM tables split by year, like: data2003, data2004, data2005, data2006, data2007, data2008, all having the same definitions. To speed up the query process, I also defined MERGE tables, like: CREATE TABLE data20032004 ... ENGINE=MERGE UNION (data2003, data2004); CREATE TABLE data20032005 ... ENGINE=MERGE UNION (data2003, data2004, data2005); CREATE TABLE data20032006 ... ENGINE=MERGE UNION (data2003, data2004, data2005, data2006); CREATE TABLE data20032007 ... ENGINE=MERGE UNION (data2003, data2004, data2005, data2006, data2007); CREATE TABLE data20032008 ... ENGINE=MERGE UNION (data2003, data2004, data2005, data2006, data2007, data2008); CREATE TABLE data20042005 ... ENGINE=MERGE UNION (data2004, data2005); etc. etc. CREATE TABLE data20072008 ... ENGINE=MERGE UNION (data2007, data2008); For example, if a user query applies to 2003..2006, my app selects the belonging MERGE table data20032006, excluding tables data2007 and data2008 for faster results. Is this assumption correct ? Or are there better ways of doing this ? I'm using: mySQL version 5.0.15-NT TIA, Cor
