Advantage of BTREE?

I create indexes without the USING BTREE clause. Is there any advantage of using BTREE开发者_运维技巧 index?

CREATE INDEX `SomeName` USING BTREE ON `tbl_Name`(`column_name`);

---

First off, depending on the Storage Engine used, you may just not have a choice (InnoDB for example is exclusively using BTREE for its index).

Also, BTREE is the default index type for most storage engines.

Now... There are cases, when using alternative index types may result in improved performance. There are (relatively rare case) when a HASH index may help. Note that when a HASH index is created, a BTREE index is also produced. That's in part due to the fact that hash indexes can only resolve equality predicates. (a condition such as WHERE Price > 12.0 could not be handled by a hash index).

In short: Keep using BTREE, whether implicitly (if BTREE is the default for the Storage used), or explicitly. Learn about the other types of indexes so that you know about them would the need arise.

Edit: (in searching cases when alternate index types may be used)
Effectively the case is rather straight forward for RTREE indexes. These are only supported, with MySQL, in the context of "SPATIAL" databases, i.e. databases which include Geo position context such as Point and other object in the GIS model).

HASH indexes are more generic (not limited to a particular application or data type), and one can generally follow one's intuitive understanding of hashes to get a hint as to when these may outperform the old-but-faithful BTREE. As indicated earlier, this would imply columns typically searched with an equal predicate. I'm guessing relatively short lookup tables and the like could benefit, depending on the effective implementation within MySQL.

---

BTREE is the default index method. You may safely omit it.

---

It depends on which storage engine you're using. For most, BTREE is the default so specifying it doesn't really change anything. For storage engines such as MEMORY/HEAP and NDB, the default is to use HASH indexes by default.

More information can be found here.

Whether or not a B-tree or a HASH index is advantageous for you from a performance perspective depends on the data and how you're accessing it. If you know you're queries are going to target exactly one row or scattered individual rows, then a HASH index may be useful. Anything other than that, I generally prefer a BTREE index as the data is sorted and thus makes range queries and those that return multi-rows more efficient.

---

searching a balanced tree means that all the leaves are at the same depth. There is no runway pointer overhead. Indeed,even larger B-trees can guarantee a small number of nodes must be retrieved to find a given key. For example, a B-tree of 10,000,000 keys with 50 keys per node never needs to retrieve more than 4 nodes to find any key. A B-tree is a special data structure format for an index that allows rapid access of the data in the index.One of the properties of this data structure is that the index is always balances.That means each node at the lowest level is equidistant from the top most node, or the root node of the tree.And each side of the index has the same number of nodes.The nodes at the lowest levels are known as leaf nodes.All other nodes are known as branch nodes.Branches points to other branches or leaf nodes.Leaf nodes store the values of the indexed columns and the rowid that points to the distinct row that has those values. The actual distribution will depend on the number of data values in each range of values in a B-tree with overall goal to reduce the number of required levels that must be traversed to get to a specific value. The advantage of a B-tree structure are:

1. All leaf blocks are of the same depth(number of values).
2. The height of the B-tree is typically pretty small.In some cases,the root node is the only leaf node and the height is 1.As the tables get more rows inserted into it,the index must grow to accommodate this.But even in tables with over 1 million rows,the B-tree idex typically has a height 3.In the very largest of tables , the height may only be 4.This means that for even the largest tables,it only takes 4 blocks to find the rowid of the row you are looking for,This is extremely efficient.
3. In the cases of randomly entered data,the B-tree stays balances automatically.In fact, the B-tree stays balances no matter what data is entered to it.
4. All blocks of a B-tree index are three quarters full(on the average),allowing insertion without rebulid. 5.B-tree provide excellent performance for all types of selects. 6.Insert,update and deleted tend to be efficient in a B-tree structure. 7.B-tree performance stays optimal even when tables from small to large.

---

The simplified answer is, if your SQL is using a LIKE statement on that field then using BTREE index should outperform a Hash Index. If you are using equal to (=) statements against that field stay with Hash Index.