"onedbguru" <onedbguru@yahoo.com> wrote:
>
> Axel Schwenke wrote:

[Fullquote, including signature]

Please don't fullquote!

> IMNSHO, using partitioned tables would be a much better alternative. I
> have administered real databases in the multi-terabyte range with this
> approach.
>
> create tablespace p0...
> create tablespace p1 etc...
>
> CREATE TABLE t1 (
> id INT,
> year_col INT
> )
> PARTITION BY RANGE (year_col) (
> PARTITION p0 VALUES LESS THAN (1991),
> PARTITION p1 VALUES LESS THAN (1995),
> PARTITION p2 VALUES LESS THAN (1999)
> );

Did you notice the word "mysql" in this groups name? Your suggestion
does not work with MySQL. In fact MySQL 5.1 does support partitions -
but it is currently BETA and the syntax is slightly different.
MERGE tables (as suggested by me) are the poor mans approximation of
partitions, available in stable MySQL editions.

However, the OPs problem is, that DELETE on a large table

a) may take a long time and
b) locks that table

His original approach (deleting small chunks) tackles a). Using an
InnoDB table would eliminate b). Partitions won't solve any of those
in the first place - only if DELETE is implemented as DROP PARTITION.


XL
--
Axel Schwenke, Senior Software Developer, MySQL AB

Online User Manual: http://dev.mysql.com/doc/refman/5.0/en/
MySQL User Forums: http://forums.mysql.com/