0

I am using mysql

I have a table called address and the table has a column called zip5 which is of type varchar(6) .

I am using query

alter table address change zip5 zip5 varchar(14);

but the query execution is taking too long I am waiting from almost 15 minutes and waiting for query to execute, the address table has 9.7 million records. Does it take this long for this amount of data or am I doing something wrong here?

Mohit Kanwar
  • 2,962
  • 7
  • 39
  • 59
mb1987
  • 437
  • 8
  • 21
  • 1
    `CHANGE` is intended for renaming columns, and I don't know what it will do if you try to rename to the same name (as you're doing). To change the data type, `MODIFY` is more appropriate: `ALTER TABLE address MODIFY zip5 VARCHAR(14);` – Michael Berkowski Oct 15 '14 at 14:29
  • Looks like this is normal check this out http://stackoverflow.com/questions/12774709/mysql-very-slow-for-alter-table-query – NMK Oct 15 '14 at 14:30
  • But 9.7M is a large table, and if an index has to be rebuilt it may take a long time. To see its progress, do `SHOW PROCESSLIST;` – Michael Berkowski Oct 15 '14 at 14:30
  • even i think so that index are causing a problem, there is index on address_id, state_id, and city_id columns in the table. but does modifying the column data-type really affect the performance of mysql – mb1987 Oct 15 '14 at 14:39
  • If there's no index on zip5 then I doubt that indexes are a factor - but I may be mistaken. – Strawberry Oct 15 '14 at 14:43
  • there is no index on zip5...deleted all the indexes on the table and tried to modify the column ,,,but still no change.. – mb1987 Oct 15 '14 at 14:47
  • Why aren't you making it a char (14)? – Flip Vernooij Oct 15 '14 at 14:47
  • i dont think so it will make any difference..but not sure – mb1987 Oct 15 '14 at 14:53

1 Answers1

0

Hmm, I dont know why but

ALTER TABLE address MODIFY zip5 varchar(14)

seems to be a bit faster. At least on my system with a comparable table-structure.

ALTER TABLE makes a copy of you table. Perhaps the bottle-neck is your HD? Do you use SSDs? or is your temporary table storage not set on a fast disk? Is the disk full?

Benvorth
  • 7,416
  • 8
  • 49
  • 70
  • database is not in ssd, it is in normal hard-drive. disk is not full. I still have 480 GB of free space.. – mb1987 Oct 15 '14 at 17:08