MySQL 5.7 Reference Manual  /  ...  /  Comparison of B-Tree and Hash Indexes

### 8.3.8 Comparison of B-Tree and Hash Indexes

Understanding the B-tree and hash data structures can help predict how different queries perform on different storage engines that use these data structures in their indexes, particularly for the `MEMORY` storage engine that lets you choose B-tree or hash indexes.

#### B-Tree Index Characteristics

A B-tree index can be used for column comparisons in expressions that use the `=`, `>`, `>=`, `<`, `<=`, or `BETWEEN` operators. The index also can be used for `LIKE` comparisons if the argument to `LIKE` is a constant string that does not start with a wildcard character. For example, the following `SELECT` statements use indexes:

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

In the first statement, only rows with ```'Patrick' <= key_col < 'Patricl'``` are considered. In the second statement, only rows with ```'Pat' <= key_col < 'Pau'``` 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 statement, the `LIKE` value begins with a wildcard character. In the second statement, the `LIKE` value is not a constant.

If you use ```... LIKE '%string%'``` and `string` is longer than three characters, MySQL uses the Turbo Boyer-Moore algorithm to initialize the pattern for the string and then uses this pattern to perform the search more quickly.

A search using ```col_name IS NULL``` employs indexes if `col_name` is indexed.

Any index that does not span all `AND` levels in the `WHERE` clause is not used to optimize the query. In other words, to be able to use an index, a prefix of the index must be used in every `AND` group.

The following `WHERE` clauses use indexes:

``````... 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 MySQL does not use an index, even if one is available. One circumstance under which this occurs is when the optimizer estimates that using the index would require MySQL to access a very large percentage of the rows in the table. (In this case, a table scan is likely to be much faster because it requires fewer seeks.) However, if such a query uses `LIMIT` to retrieve only some of the rows, MySQL uses an index anyway, because it can much more quickly find the few rows to return in the result.

#### Hash Index Characteristics

Hash indexes have somewhat different characteristics from those just discussed:

• They are used only for equality comparisons that use the `=` or `<=>` operators (but are very fast). They are not used for comparison operators such as `<` that find a range of values. Systems that rely on this type of single-value lookup are known as key-value stores; to use MySQL for such applications, use hash indexes wherever possible.

• The optimizer cannot use a hash index to speed up `ORDER BY` operations. (This type of index cannot be used to search for the next entry in order.)

• MySQL cannot determine approximately how many rows there are between two values (this is used by the range optimizer to decide which index to use). This may affect some queries if you change a `MyISAM` or `InnoDB` table to a hash-indexed `MEMORY` table.

• Only whole keys can be used to search for a row. (With a B-tree index, any leftmost prefix of the key can be used to find rows.)
{"ft_lang_label":"__label__en","ft_lang_prob":0.7592959,"math_prob":0.8206685,"size":3724,"snap":"2021-43-2021-49","text_gpt3_token_len":914,"char_repetition_ratio":0.13306452,"word_repetition_ratio":0.034375,"special_character_ratio":0.2424812,"punctuation_ratio":0.11142062,"nsfw_num_words":0,"has_unicode_error":false,"math_prob_llama3":0.98930264,"pos_list":[0],"im_url_duplicate_count":[null],"WARC_HEADER":"{\"WARC-Type\":\"response\",\"WARC-Date\":\"2021-10-22T07:28:05Z\",\"WARC-Record-ID\":\"<urn:uuid:7382d4ed-85a2-481b-8286-808fd14c2428>\",\"Content-Length\":\"90934\",\"Content-Type\":\"application/http; msgtype=response\",\"WARC-Warcinfo-ID\":\"<urn:uuid:2b51a2b6-6e3e-4097-972a-6b6274e2e038>\",\"WARC-Concurrent-To\":\"<urn:uuid:13b24345-708b-4b9d-9c0f-b31dd1caa9dc>\",\"WARC-IP-Address\":\"137.254.60.11\",\"WARC-Target-URI\":\"https://dev.mysql.com/doc/refman/5.7/en/index-btree-hash.html\",\"WARC-Payload-Digest\":\"sha1:XZKNQST3ZUQN5RQEUVN26REPRUYWEOW2\",\"WARC-Block-Digest\":\"sha1:253MK6PQFK2GLO7KARJWNSBZYZ5BLADH\",\"WARC-Identified-Payload-Type\":\"text/html\",\"warc_filename\":\"/cc_download/warc_2021/CC-MAIN-2021-43/CC-MAIN-2021-43_segments_1634323585460.87_warc_CC-MAIN-20211022052742-20211022082742-00652.warc.gz\"}"}