Is it necessary to compress tables?
Does MySQL automatically handle deleted row cleanup, or is it necessary to periodically do this manually for tables with a lot of deletions? If it's manual, what are the SQL commands to do this? Also, what happens when changes are made to a field definition? From, say, int to smallint, or tinyint to int, or varchar(255) to varchar(10)? Do temporary tables get created and the data copied over, or does unused space end up in the data files, to be collected later? Thanks. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Is it necessary to compress tables?
Since I received no responses, maybe someone can take a moment to explain why this is such a stupid question. - Original Message - From: Jim McAtee [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Wednesday, July 23, 2003 1:28 PM Subject: Is it necessary to compress tables? Does MySQL automatically handle deleted row cleanup, or is it necessary to periodically do this manually for tables with a lot of deletions? If it's manual, what are the SQL commands to do this? Also, what happens when changes are made to a field definition? From, say, int to smallint, or tinyint to int, or varchar(255) to varchar(10)? Do temporary tables get created and the data copied over, or does unused space end up in the data files, to be collected later? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Is it necessary to compress tables?
In the last episode (Jul 23), Jim McAtee said: Does MySQL automatically handle deleted row cleanup, or is it necessary to periodically do this manually for tables with a lot of deletions? If it's manual, what are the SQL commands to do this? Also, what happens when changes are made to a field definition? From, say, int to smallint, or tinyint to int, or varchar(255) to varchar(10)? Do temporary tables get created and the data copied over, or does unused space end up in the data files, to be collected later? Since I received no responses, maybe someone can take a moment to explain why this is such a stupid question. Maybe not stupid, but definitely please see the manual questions. Answers to Question 1: http://www.mysql.com/doc/en/DELETE.html http://www.mysql.com/doc/en/Optimisation.html http://www.mysql.com/doc/en/OPTIMIZE_TABLE.html Answer to Question 2: http://www.mysql.com/doc/en/ALTER_TABLE.html BTW - Please allow more than 2 hours between question and complaint. -- Dan Nelson [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re[2]: Is it necessary to compress tables?
Jim, Does MySQL automatically handle deleted row cleanup, or is it necessary to periodically do this manually for tables with a lot of deletions? If it's manual, what are the SQL commands to do this? Depends on which storage engine you are using. If you are using InnoDB tables, there's no need to vacuum tables that way, as InnoDB will do that automatically. If you use MyISAM tables, however, you might be able to speed up things quite a bit by using the OPTIMIZE TABLE command at regular intervals. That SQL command will reclaim unused space that results from deleting rows, and it will speed up table lookups: http://www.mysql.com/doc/en/OPTIMIZE_TABLE.html Also, what happens when changes are made to a field definition? From, say, int to smallint, or tinyint to int, or varchar(255) to varchar(10)? Do temporary tables get created and the data copied over, or does unused space end up in the data files, to be collected later? This sentence from the manual should make that clear: ALTER TABLE works by making a temporary copy of the original table. You can find details here: http://www.mysql.com/doc/en/ALTER_TABLE.html Regards, -- Stefan Hinz [EMAIL PROTECTED] iConnect GmbH http://iConnect.de Heesestr. 6, 12169 Berlin (Germany) Telefon: +49 30 7970948-0 Fax: +49 30 7970948-3 [filter fodder: sql, mysql, query] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Is it necessary to compress tables?
Since I received no responses, maybe someone can take a moment to explain why this is such a stupid question. Maybe not stupid, but definitely please see the manual questions. Answers to Question 1: http://www.mysql.com/doc/en/DELETE.html http://www.mysql.com/doc/en/Optimisation.html http://www.mysql.com/doc/en/OPTIMIZE_TABLE.html Answer to Question 2: http://www.mysql.com/doc/en/ALTER_TABLE.html Hey, RTFM works for me. I appreciate the links. I guess I just wouldn't normally expect an explanation of the inner workings of MySQL deletions and table optimization under a section titled DELETE Syntax. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
