>>>>> "Tim" == Tim Bunce <Tim.Bunce@stripped> writes:
>> 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...
Currently a MERGE table is just a form definition file (.frm) and a
text file (.MRG) that includes the tables that should be joined.
It's of course easier to just rewrite the .txt file with one command
than having to do a DROP + create of the merge file.
Tim> If it's a logical construct then by "faster" above I presume you mean
Tim> "simpler for the user to use alter than drop + create" because the create
Tim> should be instant regardless of the size of the underlying tables. Yes?
Tim> I'd agree that support for alter (at least to add and remove tables)
Tim> would be nice, but it's got to be a lower priority than support for indices.
For us at TCX, MERGE is already very useful for us without indexes;
We would just need to be able to do the re-mapping as easy as with our
current UNIREG tool to be able to shift more things from the UNIREG
environment to MySQL.
I agree that adding indexes to MERGE would put everything into a new
>> >> 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).
Tim> Any chance of optionally using key distribution information to avoid
Tim> searching in each of the tables? (By the way, there seems to be _very_
Tim> little information about the --analyse option to (my)isamchk. Will
Tim> that be expanded for the next release?)
I just updated the appropriate section in the MySQL manual with:
To help MySQL optimize queries better, run myisamchk
--analyze on a table after it has been loaded with relevant data. This
updates a value for each index part that indicates the average number of
rows that have the same value. (For unique indexes, this is always 1,
of course.). MySQL will use this to decide which index to
choose when you connect two tables with 'a not constant expression'.
You can check the result from the analyze run by doing SHOW
INDEX FROM table_name and examining the Cardinality column.