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


>> 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).

Jan> That's level zero optimization.

Jan> How are the big noses doing table partitioning?
Jan> I know that Informix partitions by the value of a chosen column.
Jan> They are also clever in partitioning indices
Jan> that start with the chosen column.
Jan> However, there you can partition a table
Jan> only at CREATE/ALTER TABLE time.
Jan> You cannot merge tables that were independent.
Jan> (You can with a INSERT ... SELECT ... UNION SELECT ... etc.,
Jan> but that'll copy your stuff.
Jan> You can also set up a view for the union,
Jan> but then indices won't be used in queries.)

Jan> Translating their indexing strategy to MySQL terms,
Jan> it takes some kind of "double-deck" indices:
Jan> Apart from regular indices on the mapped tables,
Jan> the merged table can be endowed with (perhaps shorter)
Jan> indices of its own. These indices store only pointers
Jan> to the mapped tables.

This would be trivial to do;  For example here at TCX we usually split
tables on year/month!

Jan> If the user is careful with populating the mapped tables,
Jan> he might see MySQL doing only one key search in the right table.


Jan> An example:
Jan> A bunch of tables have the 'day date not null' column
Jan> as the leading column in the primary key.
Jan> These tables are merged together and the merged table
Jan> has a helper index on '(day)' only.
Jan> If each table stores information from exactly one day,
Jan> this helper index will in fact be unique and it will
Jan> point any searches that fix the 'day' column
Jan> to the one table that has the data.

Yes;  This could easily be done by letting one define 'split columns'
for the MERGE table to tell MySQL how the tables are partitioned and
then generate additional indexes that MySQL can use that includes the

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