0

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.

1 Answer 1

0

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.

5
  • Doesn't mysql regenerate the indexes involving the column? On changing the collation of the column, I observed change in cardinality of the indexes that include this column. I am assuming mysql is handling it by itself.
    – Kushan Sen
    Dec 14, 2019 at 5:05
  • Is that what you meant by: An ALTER .. COLUMN will rebuild the necessary indexes. ?
    – Kushan Sen
    Dec 14, 2019 at 5:33
  • @KushanSen - Yes. (And "cardinality" is not relevant.)
    – Rick James
    Dec 14, 2019 at 21:37
  • Thanks. Also while changing collation or undoing the change there will be no data loss right as both collations belong to the same character set?
    – Kushan Sen
    Dec 17, 2019 at 4:48
  • @KushanSen - The encoding is entirely controlled by the charaset; only the rules of comparing and sorting are controlled by the collation. One charset can have many collations, but not vice versa. So, no data loss when changing only the collation. (But, since an index is ordered according to the collation, it needs rebuilding.)
    – Rick James
    Dec 17, 2019 at 15:46

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.