RE: Compressing after Deletion
David, According to the documentation, OPTIMIZE will also work on InnoDB tables. Will that produce the same result as your ALTER TABLE ? Cheers Terry - Original Message - Hi Chris, For MyISAM/BDB tables use OPTIMIZE TABLE your table name; For InnoDB tables try ALTER TABLE your table name TYPE=InnoDB; Regards David Logan Database Administrator HP Managed Services 148 Frome Street, Adelaide 5000 Australia +61 8 8408 4273 - Work +61 417 268 665 - Mobile +61 8 8408 4259 - Fax -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? Any help is greatly appreciated. Chris Hood -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
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]
Re: Compressing after Deletion
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]
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? Any help is greatly appreciated. Chris Hood -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Compressing after Deletion
Hi Chris, For MyISAM/BDB tables use OPTIMIZE TABLE your table name; For InnoDB tables try ALTER TABLE your table name TYPE=InnoDB; Regards David Logan Database Administrator HP Managed Services 148 Frome Street, Adelaide 5000 Australia +61 8 8408 4273 - Work +61 417 268 665 - Mobile +61 8 8408 4259 - Fax -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? Any help is greatly appreciated. Chris Hood -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
