Two more notes, assuming this is a MyISAM table. If this table sees concurrent inserts (many mixed INSERTs and SELECTs), you'll definitely want to run an OPTIMIZE TABLE after your purge. Large numbers of deletes or updates to variable-length fields will result in free blocks in the data file, and they'll slow down your INSERTs as MySQL tries to figure out the best way to fill the holes. That said, OPTIMIZE TABLE locks the table and rebuilds both the data file and all of its indexes, so it can be awfully slow.
If you do decide to OPTIMIZE immediately after your deletes, you can speed the entire process up significantly by using "DELETE QUICK FROM TABLE". This deletes rows but does not update the table's indexes. Running OPTIMIZE TABLE immediately thereafter will recreate those indexes for you, building them from the new, smaller table.
More on MyISAM tables: http://dev.mysql.com/doc/mysql/en/myisam-storage-engine.html
OPTIMIZE TABLE: http://dev.mysql.com/doc/mysql/en/optimize-table.html
DELETE: http://dev.mysql.com/doc/mysql/en/delete.html
____________________________________________________________ Eamon Daly
----- Original Message ----- From: <[EMAIL PROTECTED]>
To: <[EMAIL PROTECTED]>
Cc: <[EMAIL PROTECTED]>; <mysql@lists.mysql.com>
Sent: Wednesday, March 09, 2005 4:43 AM
Subject: RE: Compressing after Deletion
Note that with MySQL, unlike some other databases, you don't have to do this. If you are going to add new records to replace the deleted ones, MySQL will re-use the space freed by deletions with no special actions on your part. Of course, if you are not going to replace the deleted records, the commands suggested by David will certainly free space and probably improve performance.
Alec Cawley
"Logan, David (SST - Adelaide)" <[EMAIL PROTECTED]> wrote on 08/03/2005 22:58:12:
Hi Chris,
For MyISAM/BDB tables use OPTIMIZE TABLE <your table name>; For InnoDB tables try ALTER TABLE <your table name> TYPE=InnoDB;
-----Original Message----- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Sent: Wednesday, 9 March 2005 9:19 AM To: mysql@lists.mysql.com Subject: Compressing after Deletion
I have looked in the documentation and either I am not looking for the right thing or have simply overlooked it. But my question is this, I have a database with 35 Million records, and I need to delete about 25 million of those. After deletion I would think that I would need to compress, shrink, or otherwise optimize the database. How is that done? do I need to do it? What commands should I be looking up in the docs?
-- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
