According to [the Book][1] ![Understanding MySQL Internals][2] Page 198 says the following: - Paragraph 6 : A MyISAM B-tree consists of leaf and nonleaf nodes, or pages. - Paragraph 7 : Both leaf and nonleaf nodes key values and pointers to the record positions in the datafile. Nonleaf nodes additionally contains pointers to child nodes. Given this description - a unique index would have more nonleaf nodes. This would lend itself mode towards ordering and searching. - nonunique index would requires less nonleaf nodes. This would lend itself mode towards doing range scans (tables and index) - [A covering index][3] (which would contain all needed columns for specific SELECT queries) would combine the best and worst of both. This would allow for a range scans that would need more nonleaf nodes and provide for ordering/searching. The additional benefit would be bypassing the need for reading table data if all needed columns are present and accounted for in the index. - I mentioned other aspects in my past post https://dba.stackexchange.com/questions/20047/benefits-of-btree-in-mysql/20114#20114 #SPACE - Unique Indexes would take up more space for nonleaf nodes. - Covering indexes would need more space than a nonunqiue index but would have a higher multi-columns index cardinality that can approach the actual row count of the table. #PERFORMANCE (Heavy-Write Environment) - SELECTs would be very fast for exact keys - SELECTs that only touch a Covering Index would be extremely fast - Random INSERTs can be a nightmare due to rotations/rebalancing of nonleaf nodes - UPDATEs that change key values can be a nightmare due to rotations/rebalancing of nonleaf nodes - The higher the cardinality, the more nonleaf nodes, the better the performance - The lower the cardinality, the fewer the nonleaf nodes, the worse the performance - The more columns in the index increases the effects of the cardinality #MEMORY Depends on Storage Engine - MyISAM : The key buffer only caches index pages. Covering indexs are your best friend here - InnoDB : Buffer Pool Needs to be Big Enough to Hold Data Pages, Index Pages, and Index Page Changes (See [InnoDB Architecture][4], https://dba.stackexchange.com/questions/1/what-are-the-main-differences-between-innodb-and-myisam/2194#2194) [1]: http://www.amazon.com/Understanding-MySQL-Internals-Sasha-Pachev/dp/0596009577 [2]: https://i.sstatic.net/BNnv4.jpg [3]: http://peter-zaitsev.livejournal.com/6949.html [4]: https://i.sstatic.net/gAXdd.png