Hi Adam,
MySQL Cluster handles deletions well but is a main memory solution
still.
So it is quite ok to use it for an amount of data and then move it to
another
table when you want it to go off-line.
In 5.1 that will arrive in a short time in first alpha releases you
will have two
options.
1) MySQL Cluster will be able to store non-indexed fields on disk
2) Your MyISAM tables can be partitioned.
By partitioning you can delete a partition without affecting other
partitions.
You will also be able to move a partition to a new table and this will
be close
to immediate.
There is a forum for partitioning that you can check for latest status.
Rgrds Mikael
PS: Most disk-based indexes have problems with deletions, to reorganise
indexes
immediately after a delete is complex and most DBMS's have opted to
avoid that
complexity. So the index is still ok but not as efficient since the
packing degree is
lower.
2005-07-12 kl. 07.17 skrev Adam Dixon:
> I have a large table containing 12 varchar, 19 int fields. This table
> consitantly grows at around 7000 records per hour. With the current
> MyISAM solution on a single server - if we delete older rows from the
> table, it causes queries to this table (which are done very
> frequently) to slowdown considerably - There is probably a technical
> explaination to this;
>
> Currently we move the old data in an offline state to another table,
> then we optimize the remaining recent data, which takes along time.
> So basically using DELETE on the table ruins its speed, can anyone
> comment as to if NDBCLUSTER handles the deletion of rows better then
> MyISAM? So that we can move older data to a seperate system.
>
>
> I would love to do a 'on the fly' routine to clear up old data, eg
> select and insert into a offline area so that we can maintain the size
> of the cluster.
>
>> From what I can gather the index's become ineffective if delete is
> used on a myisam table.
>
>
> Or if anyone has any suggestions for this comments would be great.
>
> Adam
>
> --
> MySQL Cluster Mailing List
> For list archives: http://lists.mysql.com/cluster
> To unsubscribe:
> http://lists.mysql.com/cluster?unsub=1
>
>
>
Mikael Ronstrom, Senior Software Architect
MySQL AB, www.mysql.com
Jumpstart your cluster:
http://www.mysql.com/consulting/packaged/cluster.html