5

I you have a simple table and you want to alter a column without renaming it, you have (at least) 2 options CHANGE and MODIFY.

For example, if I have a column id that's a SMALLINT and I want to make it a INT I can run:

 ALTER TABLE foo MODIFY id INT(11);

or:

ALTER TABLE foo CHANGE id id INT(11);

Is there any performance benefit of one vs. the other? I know which is semantically better, but that might not be reason enough for example to refactor a large set of migration scripts if there is no end performance gain.

Ray
  • 40,256
  • 21
  • 101
  • 138
  • 2
    @RaphaëlAlthaus I'm looking for if there is benefit or performance difference. http://stackoverflow.com/questions/14767174/modify-column-vs-change-column only addresses the why one semantic is more proper than the other. – Ray Mar 07 '14 at 15:23
  • @RaphaëlAlthaus I've edited the question name to be more clear on this – Ray Mar 07 '14 at 15:24
  • This is one of those questions where the empirical evidence will outweigh anything we say. – Andy Lester Mar 07 '14 at 16:37
  • 2
    @AndyLester I think it's worth asking if anyone already has some empirical evidence before spending time testing out this myself on larger tables. A high portion of question SO can be 'you can just test it your self, or RTFM', but we use SO to save time/effort. – Ray Mar 07 '14 at 16:43
  • 2
    It's a real shame that this was marked as duplicate, as the question most definitely isn't addressed by the linked answers. For those of us with tables with millions of rows, it could make a real difference to know the answer! – coatesap Feb 19 '16 at 12:22
  • I just tested CHANGE vs MODIFY action on a table column. I changed type TEXT to LONGTEXT over 12.000.000 records. MODIFY took 293 seconds, CHANGE took 287 seconds so almost no performance difference or the difference is more likely to be other programs using some processor power too – Julesezaar Oct 07 '21 at 10:21

0 Answers0