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.
Subject: Questions about delete and optimize
>
> Hi,
>
> We are looking at various methods that we can effectively and
> efficiently
> delete lots of rows from a database and then optimize it. Our
> main concern
> is disk space - the partition we are working with is only
> 12
Hi,
We are looking at various methods that we can effectively and efficiently
delete lots of rows from a database and then optimize it. Our main concern
is disk space - the partition we are working with is only 12gigs small and
our database vary in size from 1gig (not a problem) to 11gig. In the
