Advantage of BTREE?

mysql b-tree database-indexes
shantanuo picture shantanuo · Nov 6, 2009 · Viewed 29.7k times · Source

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

mjv picture mjv · Nov 6, 2009

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.

Related questions

B-Tree vs Hash Table

In MySQL, an index type is a b-tree, and access an element in a b-tree is in logarithmic amortized time O(log(n)). On the other hand, accessing an element in a hash table is in O(1). Why is a …

Read More
how B-tree indexing works in mysql

When I create an index for a table in mysql, I see that the index_type is type BTREE. Now although I understand about btree(s), I do not quiet understand how it stores the index and how the database …

Read More
MySQL Hash Indexes for Optimization

So maybe this is noob, but I'm messing with a couple tables. I have TABLE A roughly 45,000 records I have TABLE B roughly 1.5 million records I have a query: update schema1.tablea a inner join ( SELECT DISTINCT ID, Lookup, IDpart1, …

Read More