Sample Header Ad - 728x90

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

1 vote
1 answer
2094 views
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.
Asked by Kushan Sen (41 rep)
Dec 13, 2019, 06:37 AM
Last activity: Apr 27, 2025, 04:06 AM