MySQL- Performance implications of changing a column collation from latin1_swedish_ci to latin1_bin

Question

I am a developer so was hoping someone with DBA experience could help me out here.

I am using a MySQL database (MYSQL 5.6).

I came across a situation where a particular column stores Ids generated by any external system (to be provided by user input) and hence requires case sensitivity (abcd is a different id from ABCD). It seemed pretty natural to me to change the collation for that column to latin1_bin from latin1_swedish_ci where my database and tables all use latin1_swedish_ci collation.

I use the following statement to change the collation:

ALTER TABLE T MODIFY C VARCHAR(200) BINARY NOT NULL;

Are there any performance implications of doing this change? My application does not use COLLATE specifically in any query.

Also, column C is part of an index and a unique constraint with 2 other columns say A, B in table T (A,B,C). A and B are also foreign keys in table T.

Do I need to drop the index and unique constraints and re-create them?

Also, how do I rollback this change? Will this statement do the job:

ALTER TABLE T MODIFY C VARCHAR(200) NOT NULL;

The table T looks like :

|id|A|B|C|created_at|

id is auto increment.

Please help.

Answer 1

Not significant performance difference.

It will require rebuilding any indexes involving that column. That will take time, but it is a onetime issue.

Any *_ci collation is slightly more complex than a *_bin collation, at least when doing comparisons or sorting.

If you choose to undo the change, be explicit about the collation. Your proposed ALTER hopes that the 'default' will be used.

An ALTER .. COLUMN will rebuild the necessary indexes.