Advantage of BTREE?

Question

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`); 

Answer 1

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.

Answer 2

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