> TaskID didn't lose its UNIQUEness because of INDEX(TaskID) is declared as UNIQUE, however would this index take measurable cost when manupulating over 500mil records?
When inserting a row, the updating of a non-unique index, such as
INDEX(TaskID)
can be delayed. However, with
UNIQUE(TaskID)
the INSERT must immediately verify that there is not a duplicate key.
In smaller tables, this subtle distinction is not a bid deal. With 500M rows, it _may_ lead to more disk I/O.
If the INSERTs are in TaskID order (or even approximately so), the handling of the index will not need to jump around much. On the other hand, if TaskID is random (such as with a UUID), there is likely to be a lot of thrashing for the index blocks.
Block splitting, etc:
http://dev.mysql.com/doc/refman/5.5/en/innodb-table-and-index.html