Does the "unique" column field of the column mean index with MySQL, and if so, why?

Question

Although I understand that it is very convenient to have an index in a unique column - in order to easily and efficiently check for a collision of values, I think that the programmer should be asked whether the unique column of the field should have an index or not, in terms of speed optimization by size.

As far as I understand, MySQL automatically indexes a table column that is listed as unique. This is true?

Is there some reason MySQL doesn't seem to be able to have unique columns without indexes? Yes, each insert / update of values ​​will be performed without the O (number_of_rows) operation, but, as I said, does the programmer need to tax the decision?

Just curious!

Answer 1

As far as I understand, MySQL automatically indexes a table column that is listed as unique. This is true?

Yes. This is not necessarily “automatic,” although implicit. UNIQUE is an index type, so of course this index is indexed.

Is there some reason MySQL doesn't seem to be able to have unique columns without indexes?

Because it would be foolish for MySQL to do a full table scan on each INSERT / UPDATE to defend a unique constraint.

Edit

Yes, each insert / update of the value will be performed without the O (number_of_rows) operation, but, as I said, does the programmer need to tax the decision?

Why does a programmer want to manually enforce basic data integrity beyond the storage tier? I understand where you are going with this, but the fact is an index that cannot hurt anything (it just takes a little more space), so you really should not make a decision.