This is the 7th day of my participation in the November Gwen Challenge. Check out the details: The last Gwen Challenge 2021
The hash index
Hash indexes are implemented based on hash tables, and only queries that accurately match all columns of the index are valid.
For each row of data, the storage engine computes a hash value for all index columns. Rows with different hash values and key values are computed differently. A hash index stores all hashes in the index and also stores Pointers to each row in a hash table.
Only the Memory engine in Mysql displays support for hash indexes. Hash indexes are the default indexes of Memory engine tables.
If multiple columns have the same hash value, the index stores multiple record Pointers in a linked list into the same hash record.
Disadvantages of hash indexes
A hash index contains only hash values and row Pointers and does not store field values, so you cannot use values in the index to avoid reading rows
Hash index data is not stored in order of index value, so it cannot be used for sorting.
Hash indexes also do not support matching lookups for partial index columns because hash indexes always use the entire contents of the index column to calculate the hash value.
Hash indexes only support equivalent comparison queries, not any range queries
Accessing hashed indexed data is very fast unless there are a lot of hashing conflicts. When a hash conflict occurs, the storage engine must traverse all the row Pointers in the linked list, comparing them row by row until it finds all the rows that match the criteria.
Indexes can be expensive to maintain if there are many hash conflicts.
InnoDB’s adaptive hash index
When InnoDB notices that some index values are being used very frequently, it creates a hash index in memory on top of the B-tree index, giving the B-tree index the advantages of hash indexes, such as fast hash lookups.
Other index
In addition to the hash index b-tree index, there are other indexes, such as spatial data index, full-text index, etc.
Spatial indexes index data from all dimensions. When querying, you can effectively combine queries with any dimension
Full-text indexes look for keywords in text rather than directly comparing values in the index.