Does making a field unique make it indexed?

If I make a unique constraint on a field, do I also need to make an index on that field in order to get a scalable insert time? Or is this done for me (even if the index it uses isn't publicly accessible?)

Specifically, I'm working with Apache Derby for prototyping, although I will probably be moving it to MySQL in the semi-near future. I'm also hoping there might be something in the SQL standard that says something about this.

I will never have a need to search by this field, so I would rather not make a useless index. But I'd rather have a useless index than have an O(n) insert time.

PRIMARY KEY >= UNIQUE >= INDEX == KEY

InnoDB data is ordered by the PK. MyISAM PK acts the same as UNIQUE.

INSERT must add a "row" to each and every index (of any kind) that you have. This takes some time. (Usually, not enough time to matter.) Indexes are all stored in BTree format. MyISAM BTree blocks are 1KB; InnoDB uses 16KB.

Inserting into InnoDB updates the PK and the data simultaneously.

Inserting into MyISAM usually "appends" the data to the .MYD. Separately, it adds a row to the PK (if any).

INSERT has to first verify that there is no duplicate key for any PRIMARY or UNIQUE key. This is done by using the index. And, hence, why the UNIQUE and FOREIGN KEY CONSTRAINTs really build indexes. This is O(logN), but usually CPU, not I/O, because of efficient caching.

StackExchange Database Administrators Q#2783, answer score: 4