Re: Should I use a MERGE table?
Hrm... so it is going to need to rebuild all the indexes, not just the table I am adding... doh... oh well, should still be faster, I'd guess. Probably my best bet in this case is to create a new merged table with the new table and all the other old ones, then replace the old merged table with the new merged table. I will try it out tomorrow and see... no problems with it being the same, I will be generating the tables from a perl script :) On Thu, Nov 15, 2001 at 03:04:35PM -0800, Bill Adams wrote: > "Anthony R. J. Ball" wrote: > > > Ok, so to follow... Paul Dubois told me the little > > RENAME TABLE trick to swap out a table... Will that work > > on a table in a MERGE, or are there different hoops to > > jump through? > > Your table names need to be unique. Then it should work just fine. > > o If you do rename a table that is part of the merge, you will need to rebuild > the merge table. > > o If you want to reindex the tables that are merged, be sure to either drop the > index in the merge table or drop the merge table BEFORE you reindex the real > tables. This is not fatal if you forget: just you might get weird errors. > > o If you do a SELECT ... WHERE indexed_column=value and get back a bunch of > records where everything is blank, your indexes (or possibly columns) are not the > same between the real tables and/or merge table. Remember ORDER MATTERS! > > b. > > > > > > > > > > On Thu, Nov 15, 2001 at 02:01:44PM -0800, Bill Adams wrote: > > > "Anthony R. J. Ball" wrote: > > > > > > > I was just reading DOCS for MERGE tables... and they > > > > look like exactly what I want. > > > > > > > > I have a large lookup table that I use to track down > > > > where I want to find my data, which gets loaded into > > > > different tables at different times of the day > > > > (financial data). Now I am loading all the data into > > > > one table, but since this table needs to stay available > > > > I have to insert into it with lots of keyed data. > > > > > > > > I am thinking that I can create a new table, and > > > > replace the proper underlying table with the new > > > > data. That way I don't slow doen the lookup table > > > > when I am loading, and I can load my data MUCH faster > > > > by loading with no keys. > > > > > > > > Am I right in thinking this way. So I can make > > > > a muni lookup and a CMO lookup and an MBS lookup > > > > but just select from the merged table for a cusip > > > > in any of them? > > > > > > > > This is all read only data (except the initial load) > > > > > > > > This would be very good... since I want the data to > > > > load quick, and may be loading hundreds of thousands > > > > of records at a time. > > > > > > > > > > Merge table sound like they fit the bill. Beware that they are very finicky > > > and the columns MUST be the same and the index order must be the same. > > > Search the lists (esp. for my name) for more info. > > > > > > b. > > > > > > > > > > -- > > ___ __ ____ _ _ _ _ > > / __)( )( ) /__\( \/ )( ___) ( \( )( ___)(_ _) > > \__ \ )(__)( /(__)\\ / )__)) ( )__) )( > > (___/(__)(__)(__)\/ ()()(_)\_)() (__) > > Once I thought I was wrong - but I was mistaken > > -- > Bill Adams > TriQuint Semiconductor > > > > > - > Before posting, please check: >http://www.mysql.com/manual.php (the manual) >http://lists.mysql.com/ (the list archive) > > To request this thread, e-mail <[EMAIL PROTECTED]> > To unsubscribe, e-mail <[EMAIL PROTECTED]> > Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php > -- ___ __ ____ _ _ _ _ / __)( )( ) /__\( \/ )( ___) ( \( )( ___)(_ _) \__ \ )(__)( /(__)\\ / )__)) ( )__) )( (___/(__)(__)(__)\/ ()()(_)\_)() (__) I don't suffer from insanity, I enjoy every minute of it. - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Should I use a MERGE table?
"Anthony R. J. Ball" wrote: > Ok, so to follow... Paul Dubois told me the little > RENAME TABLE trick to swap out a table... Will that work > on a table in a MERGE, or are there different hoops to > jump through? Your table names need to be unique. Then it should work just fine. o If you do rename a table that is part of the merge, you will need to rebuild the merge table. o If you want to reindex the tables that are merged, be sure to either drop the index in the merge table or drop the merge table BEFORE you reindex the real tables. This is not fatal if you forget: just you might get weird errors. o If you do a SELECT ... WHERE indexed_column=value and get back a bunch of records where everything is blank, your indexes (or possibly columns) are not the same between the real tables and/or merge table. Remember ORDER MATTERS! b. > > > On Thu, Nov 15, 2001 at 02:01:44PM -0800, Bill Adams wrote: > > "Anthony R. J. Ball" wrote: > > > > > I was just reading DOCS for MERGE tables... and they > > > look like exactly what I want. > > > > > > I have a large lookup table that I use to track down > > > where I want to find my data, which gets loaded into > > > different tables at different times of the day > > > (financial data). Now I am loading all the data into > > > one table, but since this table needs to stay available > > > I have to insert into it with lots of keyed data. > > > > > > I am thinking that I can create a new table, and > > > replace the proper underlying table with the new > > > data. That way I don't slow doen the lookup table > > > when I am loading, and I can load my data MUCH faster > > > by loading with no keys. > > > > > > Am I right in thinking this way. So I can make > > > a muni lookup and a CMO lookup and an MBS lookup > > > but just select from the merged table for a cusip > > > in any of them? > > > > > > This is all read only data (except the initial load) > > > > > > This would be very good... since I want the data to > > > load quick, and may be loading hundreds of thousands > > > of records at a time. > > > > > > > Merge table sound like they fit the bill. Beware that they are very finicky > > and the columns MUST be the same and the index order must be the same. > > Search the lists (esp. for my name) for more info. > > > > b. > > > > > > -- > ___ __ ____ _ _ _ _ > / __)( )( ) /__\( \/ )( ___) ( \( )( ___)(_ _) > \__ \ )(__)( /(__)\\ / )__)) ( )__) )( > (___/(__)(__)(__)\/ ()()(_)\_)() (__) > Once I thought I was wrong - but I was mistaken -- Bill Adams TriQuint Semiconductor - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Should I use a MERGE table?
Ok, so to follow... Paul Dubois told me the little RENAME TABLE trick to swap out a table... Will that work on a table in a MERGE, or are there different hoops to jump through? On Thu, Nov 15, 2001 at 02:01:44PM -0800, Bill Adams wrote: > "Anthony R. J. Ball" wrote: > > > I was just reading DOCS for MERGE tables... and they > > look like exactly what I want. > > > > I have a large lookup table that I use to track down > > where I want to find my data, which gets loaded into > > different tables at different times of the day > > (financial data). Now I am loading all the data into > > one table, but since this table needs to stay available > > I have to insert into it with lots of keyed data. > > > > I am thinking that I can create a new table, and > > replace the proper underlying table with the new > > data. That way I don't slow doen the lookup table > > when I am loading, and I can load my data MUCH faster > > by loading with no keys. > > > > Am I right in thinking this way. So I can make > > a muni lookup and a CMO lookup and an MBS lookup > > but just select from the merged table for a cusip > > in any of them? > > > > This is all read only data (except the initial load) > > > > This would be very good... since I want the data to > > load quick, and may be loading hundreds of thousands > > of records at a time. > > > > Merge table sound like they fit the bill. Beware that they are very finicky > and the columns MUST be the same and the index order must be the same. > Search the lists (esp. for my name) for more info. > > b. > > -- ___ __ ____ _ _ _ _ / __)( )( ) /__\( \/ )( ___) ( \( )( ___)(_ _) \__ \ )(__)( /(__)\\ / )__)) ( )__) )( (___/(__)(__)(__)\/ ()()(_)\_)() (__) Once I thought I was wrong - but I was mistaken - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Should I use a MERGE table?
"Anthony R. J. Ball" wrote: > I was just reading DOCS for MERGE tables... and they > look like exactly what I want. > > I have a large lookup table that I use to track down > where I want to find my data, which gets loaded into > different tables at different times of the day > (financial data). Now I am loading all the data into > one table, but since this table needs to stay available > I have to insert into it with lots of keyed data. > > I am thinking that I can create a new table, and > replace the proper underlying table with the new > data. That way I don't slow doen the lookup table > when I am loading, and I can load my data MUCH faster > by loading with no keys. > > Am I right in thinking this way. So I can make > a muni lookup and a CMO lookup and an MBS lookup > but just select from the merged table for a cusip > in any of them? > > This is all read only data (except the initial load) > > This would be very good... since I want the data to > load quick, and may be loading hundreds of thousands > of records at a time. > Merge table sound like they fit the bill. Beware that they are very finicky and the columns MUST be the same and the index order must be the same. Search the lists (esp. for my name) for more info. b. - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
