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 aspects in my past post Benefits of BTREE in MySQL

#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, What are the main differences between InnoDB and MyISAM?)

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 aspects in my past post Benefits of BTREE in MySQL

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, What are the main differences between InnoDB and MyISAM?)

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 aspects in my past post Benefits of BTREE in MySQL

#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, What are the main differences between InnoDB and MyISAM?)

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 aspects in my past post Benefits of BTREE in MySQL

#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, What are the main differences between InnoDB and MyISAM?)

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 aspects in my past post Benefits of BTREE in MySQL

#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

#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, What are the main differences between InnoDB and MyISAM?)

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 aspects in my past post Benefits of BTREE in MySQL

#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, What are the main differences between InnoDB and MyISAM?)