MySQL: Does a 'unique' column field imply an index, and if so, why?

Question

Hi all,

Although I understand that it is very convinient to have an index on a unique column - to easily and efficiently check for value collision, I think it should be up to the programmer to specify whether a unique field column is to have an index or not, with regards to size- vs speed- optimization.

As far as I understand MySQL automatically indexes a table column that is specified as unique. Is it so?

Is there any reason MySQL seemingly cannot have unique columns WITHOUT indexes? Yes, every value insertion/update will be O(number_of_rows) operation without one, but like I said, shouldn't the programmer be taxed with the decision?

Just curious!

Answer 1

As far as I understand MySQL automatically indexes a table column that is specified as unique. Is it so?

Yes. It's not necessarily 'automatic' though, it's implicit. UNIQUE is type of index, so of course that column is indexed.

Is there any reason MySQL seemingly cannot have unique columns WITHOUT indexes?

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

Edit:

Yes, every value insertion/update will be O(number_of_rows) operation without one, but like I said, shouldn't the programmer be taxed with the decision?

Why would a programmer want to manually enforce basic data integrity outside of the data storage layer? I understand where you're going with this, but the fact is an index can't possibly hurt anything (it just takes a bit more space) - so there really isn't a decision to consider.