Re: Partitioning in MySQL: Range Partitioning

[Dave Pullin]

I have an application that handles billion+ row tables using the MERGE engine on MyISAM tables that are built and managed by, in effect, simulating what you are doing with Partitions.

It is very useful to be able to manipulate the partitions as separate tables. In some cases my application is simulating optimizations that you probably do - like query only the table/partition that "it knows" has the answers. Another case is doing simultaneous updates on separate tables (updates that would lock out each other if done on the "whole table".).

Another common case is to make a new version of a table/partition and then swapping out the old and in the new. My application has to do some fairly complex locking but it is worth it since manipulation of the whole table takes days or weeks.

So, my question is, can a partition be manipulated as a distinct table?

(You obviously have some issues over whether the range condition holds on a table that is swapped in).

I am assuming that the implementation for indices is the same as MERGE ..except that you an use the partition condition to optimize. Will you use the partition condition to avoid openning tables/partitions (and ease the limit of file descriptors)?