Hi,
Dave Pullin wrote:
> 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".).
>
I hope to do a similar thing by supporting syntax like
FROM t1 USE PARTITION (p0, p1)...
It is not designed yet but is on the TODO list for 5.1, whether it
makes it or not is as usual uncertain as with all planning.
> 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.
>
This is handled by ALTER TABLE ADD/DROP/REORGANIZE PARTITION which is already in
5.1 (not with the proper implementation yet, this is still in code review)
> So, my question is, can a partition be manipulated
> as a distinct table?
>
This is the goal by using the above mentioned stuff.
> (You obviously have some issues over whether the
> range condition holds on a table that is swapped
> in).
>
There will be a set of optimisations to avoid scanning partitions that
isn't needed as deduced from WHERE clause.
> 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)?
If one uses FROM t1 USE PARTITION (p0,p1) this will be the case. Deduction from the
WHERE clause to avoid open+lock unused partitions is still an open issue to solve. Avoiding
the scans of unused partitions is already implemented in 5.1 for a first step and more steps
is in the works.
So in general from your description it looks to me as if partitioning in 5.1 should be very
interesting for you.
Rgrds Mikael
Mikael Ronstrom
Senior Software Architect, MySQL AB
My blog:
http://mikaelronstrom.blogspot.com