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
