If your table testtab is populated, neither suggestion is efficient. You could the following instead:
# # Create an empty table `testtab_copy` # 1) CREATE TABLE testtab_copy LIKE testtab; 2) Do either of you suggestions: ALTER TABLE testtab ADD COLUMN c_col char(4) FIRST,ADD COLUMN b_col char(4) FIRST,ADD COLUMN a_col char(4) FIRST; or ALTER TABLE testtab ADD COLUMN c_col char(4) FIRST; ALTER TABLE testtab ADD COLUMN b_col char(4) FIRST; ALTER TABLE testtab ADD COLUMN a_col char(4) FIRST; 3) INSERT INTO testtab_copy (d_col) SELECT d_col FROM testtab; 4) DROP TABLE testtab; 5) ALTER TABLE testtab_copy RENAME testtab; Rolando A. Edwards MySQL DBA (CMDBA) 155 Avenue of the Americas, Fifth Floor New York, NY 10013 212-625-5307 (Work) 201-660-3221 (Cell) AIM & Skype : RolandoLogicWorx redwa...@logicworks.net http://www.linkedin.com/in/rolandoedwards -----Original Message----- From: Matt Neimeyer [mailto:m...@neimeyer.org] Sent: Friday, September 04, 2009 3:53 PM To: mysql@lists.mysql.com Subject: ALTER TABLE order / optimization Given table: CREATE TABLE testtab (d_col CHAR(4)); Question 1: It appears that there is no "harm" in just appending directives onto the alter table command even if the order doesn't make sense. It appears the parser figures it out... For example... ALTER TABLE testtab ADD COLUMN c_col char(4) FIRST, ADD COLUMN b_col char(4) FIRST, ADD COLUMN a_col char(4) FIRST; ...does end up with a_col then b_col then c_col then d_col... but does it matter and I doing something wrong? Question 2: Is that any more efficient than doing... ALTER TABLE testtab ADD COLUMN c_col char(4) FIRST; ALTER TABLE testtab ADD COLUMN b_col char(4) FIRST; ALTER TABLE testtab ADD COLUMN a_col char(4) FIRST; If it's NOT more efficient then I won't bother rewriting this one app which runs slowly to join them up because it certain is easier to read and debug with each modification on its own line. Thanks! Matt -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=redwa...@logicworks.net -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=arch...@jab.org
