List:General Discussion« Previous MessageNext Message »
From:Rolando Edwards Date:September 4 2009 8:51pm
Subject:RE: ALTER TABLE order / optimization
View as plain text  
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
redwards@stripped
http://www.linkedin.com/in/rolandoedwards

-----Original Message-----
From: Matt Neimeyer [mailto:matt@stripped] 
Sent: Friday, September 04, 2009 3:53 PM
To: mysql@stripped
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=1

Thread
ALTER TABLE order / optimizationMatt Neimeyer4 Sep
  • RE: ALTER TABLE order / optimizationRolando Edwards4 Sep