According to the Book
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 (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 aspect in my past post Benefits of BTREE in MySQL