2) Your OPTIMIZE statement does cause mysql to create a temporary table,
which eventually replaces your current one.
I suggest you try something along the lines of this and compare speed:
- LOCK TABLES ..
- CREATE TABLE `` (..) # identical table
- INSERT INTO `` SELECT * FROM `` WHERE date < (NOW(
ay, February 01, 2007 3:36 PM
To: 'Ian Barnes'; mysql@lists.mysql.com
Subject: RE: Questions about delete and optimize
Another way to do it would be to select the data you want to keep into a
table on another file system, truncate the existing table, optimize it,
then
reload it with the dat
Ian, based on your needs (regularly deleting everything morre than X
months old), I recommend you look into using the MERGE engine.
Essentially, it is multiple MyISAM tables that appear as one, and
lopping off the oldest data is as simple as redfining the MERGE and
then dropping the oldest table.
Another way to do it would be to select the data you want to keep into a
table on another file system, truncate the existing table, optimize it, then
reload it with the data you saved.
I can't say that I've tried this, and have no idea how long it would take or
even if it would work.
Regards,
Je
