Comparison between B-Tree indexes and Hash Indexes

Comparison between B-Tree indexes and Hash Indexes

Understanding the B-tree and hash data structures can help predict the query performance differences of different indexes in different storage engines, especially memory storage engines that allow you to select B-tree or hash indexes.

 

B-Tree index features B-tree indexes can be used to compare columns using the =,>, >=, <, <= or BETWEEN operator. If the LIKE parameter is a constant string that does not start with a wildcard, you can also use this index. For example, the following SELECT statement uses an index:

SELECT * FROM tbl_name WHERE key_col LIKE 'Patrick%';SELECT * FROM tbl_name WHERE key_col LIKE 'Pat%_ck%';

In the first sentence, only records of 'Patrick '<= key_col <'patterl' are considered. In the second sentence, only 'pat '<= key_col <'pau' records are considered.
The following SELECT statements do not use indexes:

SELECT * FROM tbl_name WHERE key_col LIKE '%Patrick%';SELECT * FROM tbl_name WHERE key_col LIKE other_col;

In the first sentence, the LIKE value starts with a wildcard. In the second sentence, the LIKE value is not a constant.
If you use :... LIKE '% string %'. The string must not exceed three characters. MySql uses the Turbo Boyer-Moore algorithm to initialize the string expression, and use this expression to make the query faster.
If the col_name column creates an index, you can use this index for a query that uses col_name is null.
Any index that does not cover all AND-level conditions in the WHERE clause will not be used. In other words, to use an index, the leading column of the index must be used in each AND combination.
The following WHERE clause uses the index:

... WHERE index_part1=1 AND index_part2=2 AND other_column=3    /* index = 1 OR index = 2 */... WHERE index=1 OR A=10 AND index=2    /* optimized like "index_part1='hello'" */... WHERE index_part1='hello' AND index_part3=5    /* Can use index on index1 but not on index2 or index3 */... WHERE index1=1 AND index2=2 OR index1=3 AND index3=3;

These WHERE clauses do not use indexes:

    /* index_part1 is not used */... WHERE index_part2=1 AND index_part3=2    /*  Index is not used in both parts of the WHERE clause  */... WHERE index=1 OR A=10    /* No index spans all rows  */... WHERE index_part1=1 OR index_part2=10

Sometimes, even if an index is available, MySql does not use any index. One of the scenarios where this happens is that the optimizer estimates that using this index will require MySql to access the vast majority of records in this table. In this case, a table scan may be faster because it requires a smaller number of queries. However, if such a query uses LIMIT to retrieve only a small number of records, MySql still uses indexes because it can locate this record more quickly and return it.

 

The characteristics of Hash indexes have different characteristics compared with those discussed earlier:
Hash indexes can only be used for equal comparison using the = or <=> operator (but faster ). Hash indexes cannot be used for comparison operators such as <to find a range value. The system that relies on such single-value search is called "key-value storage"; for such systems, use hash indexes whenever possible. The optimizer cannot use hash indexes to accelerate the order by operation. This type of index cannot be used to find the next entry in sequence. MySql cannot use the hash index to estimate the number of rows between two values (in this case, the range optimizer determines which index to use ). If you convert a MyISAM or InnoDB table into a memory table with a hash index, some queries may be affected. To search for a row of records, you must perform a full key match. In B-tree indexes, any left prefix of the key can be used to find records.