Is there a procedure required for updating mysql column types?

Question

I'm trying to optimize my mysql tables (MyISAM) and I changed some column types with phpmyadmin, mostly from int to smallint, medium int, etc. After I did that mysql really bogged down and the server load went really high. I did this on a live site, which I figured was the problem, but even after putting the site into maintenance mode and waiting for the load to drop down, the load still went crazy afterwards. In the end I have to drop the tables and recreate them with their original column types for things to work back smoothly... So is there anything special I should be doing before and after changing column types?

Would joining tables with two different column types also create a problem? I.E. id INT(11) joining with MEDIUMINT(8). I think at one point I did that.

Answer 1

You said:

"Would joining tables with two different column types also create a problem? I.E. id INT(11) joining with MEDIUMINT(8). I think at one point I did that."

Yes. That could kill performance because when you do a join on 2 columns of different types, the RBDMS has to perform an on the fly type conversion of every single value in one of the columns to match the type in the other column so that it can do the comparison. For this reason, columns that you are joining on should always be of the same type, preferably with a foreign key too (if you're using the InnoDB table type).