List:General Discussion« Previous MessageNext Message »
From:Michael Widenius Date:February 24 2000 2:55am
Subject:Re: Using MERGE library?
View as plain text  
Hi!

>>>>> "Tim" == Tim Bunce <Tim.Bunce@stripped> writes:

Tim> On Wed, Feb 23, 2000 at 10:02:27PM +0200, Michael Widenius wrote:
>> 
>> >>>>> "Greg" == Greg Fast <gdf@stripped> writes:
>> 
Greg> I have an application for which it would be very convenient to be able
Greg> to treat multiple tables as one table.  (Specifically, I have
Greg> time-series data which periodically gets partially moved to archival
Greg> storage to save space, but I would like to be able to occasionally run
Greg> various summary calculations on the whole data set.)
>> 
>> The MERGE libary should be able to handle the above without any
>> problem; We already us it here at TCX for similar things and it's
>> pretty stable.
>> 
>> The main thing missing in MySQL are:
>> 
>> - Adding the MERGE table option to sql_yacc.yy
>> 
>> - Adding a syntax to create the map a number of tables on a merge
>> table;  For example: 
>> MERGE table1,table2,table3 ... ON merge_table.
>> 
>> - Fixing that one can change the mapping while someone else is working
>> on the table (This is the only hard part left).

Tim> Why allow changes at all? Why not let people set up their own 'merges'?

Because if you are working on a set of tables you may want to change
the set sooner or later.  One could do this with 'drop table' + create, but it
would be nice to be able to do it faster...

Tim> I'm assuming that the "merge" is a logical, not a physical, construct
Tim> similar in effect to:
Tim> 	create view foo as
Tim> 	select * from t1 union select * from t2 union select * from t3
Tim> Is that right?

Yes.

>> I shall ask if someone of the MySQL developers would be interested in
>> fixing this real soon.

Tim> Any chance of adding support for using the indices on the underlying tables?
Tim> (The manual says "We will add indexes to this in the near future".)

To do this would probably be 2-4 days of work for someone that knows
the code; We will do this as soon as we have got the other even more
critical stuff done.

Tim> With one table per day and 80 million rows in each I'm not going to get
Tim> very far without indices!

I agree; The idea is that the MERGE tables should be able to directly
use the indexes on the underlying tables;  It's just a question of
simulating the normal key functions on the mapped tables (In other
words; When you search after a row you should have to do a key search
on each of the merged tables).

Regards,
Monty
Thread
Using MERGE library?Greg Fast23 Feb
  • Using MERGE library?Michael Widenius23 Feb
    • Re: Using MERGE library?Tim Bunce23 Feb
      • Re: Using MERGE library?Michael Widenius24 Feb
        • Re: Using MERGE library?Dan Nelson24 Feb
        • Re: Using MERGE library?Tim Bunce24 Feb
          • Re: Using MERGE library?Michael Widenius24 Feb
  • Re: Using MERGE library?Jan Dvorak24 Feb
    • Re: Using MERGE library?Michael Widenius24 Feb