Benefits of BTREE in MySQL What are the pros and cons of using a BTREE index in MySQL, regarding query speed, disk storage and memory usage?


Does BTREE provide easier iteration in increasing order ?
What kind of queries would benefit from a BTREE ?
Are there any disadvantages of using BTREE index ?
Does it increase space or indexing time?
Regardless of the Storage Engine (MyISAM or InnoDB), when it comes to BTREEs, you must make sure you understand the following characteristics:


Keys should be as small as possible
Random Keys for PRIMARY KEYs


Insertions (Bulk or Programmatic) will perform root node and internal splitting periodically 
Introduces Overhead early in an index's life
Breeds node fragmentation (especially for index pages)
Causes Index Scans for Queries to be performed Randomly

Ordered Keys for PRIMARY KEYs


Bulk Ordered Insertions delay root node and internal splitting
Reloading data via mysqldump files and LOAD DATA INFILE commands promote the use of  sorting mechanisms to address index initialization/reorganization (See my Oct 26, 2012 post: How badly does innodb fragment in the face of somewhat out-of-order insertions?)
Programmatic Ordered Insertions promote root node and internal splitting of index pages in 45% of the cases
Delays creation of Overhead
Prevents node fragmentation
Causes Index Scans for Queries to be performed Sequentially (less disk I/O)



When it comes to BTREEs in InnoDB, they tend to be more bloated than that of its counterparts MyISAM because of InnoDB's gen_clust_index, where row data live.

The PRIMARY KEY of an InnoDB table points right to its gen_clust_index. Secondary indexes always include a PRIMARY KEY entry. If you run a query that uses a Secondary Index and also has non-indexed columns in the WHERE clause, you could easily be doing two Index Lookups. With that in mind, you need to make sure all Secondary Indexes have all the needed columns for you queries' WHERE clauses (a.k.a. Covering Index).