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 `table_tmp` (..) # identical table
- INSERT INTO `table_tmp` SELECT * FROM
, 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 data you saved.
I can't say
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
,
Jerry Schwartz
Global Information Incorporated
195 Farmington Ave.
Farmington, CT 06032
860.674.8796 / FAX: 860.674.8341
-Original Message-
From: Ian Barnes [mailto:[EMAIL PROTECTED]
Sent: Thursday, February 01, 2007 4:23 PM
To: mysql@lists.mysql.com
Subject: Questions about delete
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
