Why Unique Indexes are Bad by Zardosht Kasheff.
From the post:
Before creating a unique index in TokuMX or TokuDB, ask yourself, “does my application really depend on the database enforcing uniqueness of this key?” If the answer is ANYTHING other than yes, do not declare the index to be unique. Why? Because unique indexes may kill your write performance. In this post, I’ll explain why.
Unique indexes are a strange beast: they have no impact on standard databases that use B-Trees, such as MongoDB and MySQL, but may be horribly painful for databases that use write optimized data structures, like TokuMX’s Fractal Tree(R) indexes. How? They essentially drag the Fractal Tree index down to the B-Tree’s level of performance.
When a user declares a unique index, the user tells the database, “please help me and enforce uniqueness on this index.” So, before doing any insertion into a unique index, the database must first verify that the key being inserted does not already exist. If the possible location of the key is not in memory, which may happen if the working set does not fit in memory, then the database MUST perform an I/O to bring into memory the contents of the potential location (be it a leaf node in a tree, or an offset into a memory mapped file), in order to check whether the key exists in that location.
(…)
Zardosht closes by recommending if your application does require unique indexes that you consider re-writing it so it doesn’t.
Ouch!
Not a mark against Fractal Tree(R) indexes but certainly a consideration in deciding to adopt technology using them.
Would be nice if this type of information could be passed along as more than sysadmin lore.
Like a plugin for your browser that at your request highlights products or technologies of interest and on mouse-over displays known limitations or bugs.
The sort of things that vendors loath to disclose.