{"id":106156,"date":"2025-05-06T06:38:00","date_gmt":"2025-05-06T06:38:00","guid":{"rendered":"https:\/\/www.red-gate.com\/simple-talk\/?p=106156"},"modified":"2025-04-08T19:31:41","modified_gmt":"2025-04-08T19:31:41","slug":"mysql-index-overviews-hash-indexes","status":"publish","type":"post","link":"https:\/\/www.red-gate.com\/simple-talk\/databases\/mysql\/mysql-index-overviews-hash-indexes\/","title":{"rendered":"MySQL Index Overviews: Hash Indexes"},"content":{"rendered":"\n

Welcome back to the MySQL indexing series! We\u2019ve already covered the nuances surrounding indexes in MySQL<\/a> as well as walked you through B-tree indexes<\/a>.<\/p>\n\n\n\n

MySQL, however, has many other index types it can offer for your use case, and one of those indexes are hash indexes, too.<\/p>\n\n\n\n

What are Hash Indexes?<\/h2>\n\n\n\n

In MySQL, hash indexes are indexes that are used in queries that use the equality operators like =<\/code> or <=><\/code> (which is the MySQL NULL<\/code> safe equality operator, equivalent to the SQL Standard IS NOT DISTINCT<\/code> FROM<\/code>). Hash indexes are not used in other situations, so they can be useful for things like random or generated PRIMARY KEY values where you are looking up single rows and not needing to order rows or searching for a range of values.<\/p>\n\n\n\n

As per the documentation<\/a>, MySQL only supports the use of hash indexes for the MEMORY<\/code> storage engine, and these kinds of indexes are known to facilitate blazing-fast single-row searches through data residing in memory. Hash indexes may start to become an option when index structures can save your disk in return for occupying computational power and the memory available on your server.<\/p>\n\n\n\n

Note: Such indexes can also be defined within other storage engines (I\u2019ll get to that in a second); however, in use cases not revolving around the MEMORY<\/code> storage engine, hash indexes are likely to be turned into B-tree indexes.<\/p>\n\n\n\n

The Subtleties of Hash Indexes<\/h2>\n\n\n\n

Hash indexes are not as widely implemented as B-Tree indexes, and as mentioned, part of that is their narrow use case. But even though they can be useful, there are a few concerns that you need to have to be aware of:<\/p>\n\n\n\n

For the hash table, one needs to know the number of rows likely to be within the table<\/strong>.<\/em> That helps with determining whether the amount of memory available within our server will be sufficient to store hash indexes on our data.<\/p>\n\n\n\n

Hash indexes may have to <\/i><\/b><\/em><\/span>be rebuilt from time to time for them to be usefu<\/em>l. <\/strong>Rebuilding hash indexes may become necessary when your data changes considerably\u2014if you have larger or smaller tables than expected, are not shutting down your server, and want your index to shrink, hash indexes may have to be rebuilt.

That\u2019s where we come to the upsides of storing such indexes in the memory of your server because as our data grows, that could add an overhead on our disk I\/O, but because hash indexes primarily work with data residing in memory, the rebuilding of such index types is likely to be significantly faster.<\/p>\n\n\n\n

Hash indexes are used for in-memory tables.<\/strong> <\/em>Since the only way to acquire and work with in-memory tables within MySQL and its counterparts is the usage of MEMORY<\/code> storage engine, the use cases of hash indexes will also involve MEMORY<\/code>-based tables within MySQL. <\/p>\n\n\n\n

Aside from that, it would be a shame not to note that disk-based storage engines such as InnoDB provide support for a type of hash index called Adaptive Hash Indexes. More on this later in the article.<\/p>\n\n\n\n

Hash Indexes in MariaDB<\/h2>\n\n\n\n

Now that you\u2019ve familiarized yourself with hash indexes, it\u2019s time to dig into what makes them tick. I\u2019ll use a demo table to demonstrate hash indexes in action. Its structure will be defined like so (note \u2013 here, we don\u2019t denote <\/em>MEMORY<\/code> as our storage engine to demonstrate how MariaDB \u201cthinks in InnoDB\u201d internally when hash indexes are defined<\/em>):<\/p>\n\n\n\n

DROP TABLE IF EXISTS TABLE `demo_table`;\n\nCREATE TABLE `demo_table` (\n  `id` INT(20) NOT NULL AUTO_INCREMENT PRIMARY KEY,\n  `first_name` VARCHAR(50) NOT NULL,\n  `last_name` VARCHAR(50) NOT NULL DEFAULT \u2018\u2019,\n  `email` VARCHAR(50) NOT NULL DEFAULT \u2018\u2019,\n  `gender` VARCHAR(50) NOT NULL DEFAULT \u2018\u2019,\n  `ip` VARCHAR(50) NOT NULL DEFAULT \u2018\u2019\n);<\/pre><\/div>\n\n\n\n
After we have the InnoDB table up and running, we will also drop a hash-based index on top of it using the CREATE INDEX<\/code> query:<\/p>\n\n\n\n
CREATE INDEX `hash_idx` ON `demo_table`(`first_name`) USING HASH;<\/pre><\/div>\n\n\n\n
When we combine the CREATE INDEX<\/code> clause with the CREATE TABLE<\/code> query in phpMyAdmin, we have the following result:<\/p>\n\n\n\n
\"D:\\Misc\\Redgate\\Blogs\\August<\/figure>\n\n\n\n

 Image 1 \u2013 Creating Hash Indexes when Creating a Table in MariaDB<\/strong><\/p>\n\n\n\n
After we execute the code and then inspect the structure of our table once, we can see that the hash index is indeed a BTREE<\/code> index under the hood \u2013 that happened because the default table storage engine definition for all tables that don\u2019t have the storage engine defined is InnoDB<\/em>:<\/p>\n\n\n\n
\"D:\\Misc\\Redgate\\Blogs\\August<\/figure>\n\n\n\n

  Image 2 \u2013 The Hash Index you expected to create is actually created as a BTREE Index<\/strong><\/p>\n\n\n\n
Let\u2019s now try doing the same thing with a table running the MEMORY<\/code> storage engine instead (uses the same code, with one small change to the CREATE TABLE<\/code> statement to denote the usage of the MEMORY<\/code> storage engine):<\/p>\n\n\n\n
DROP TABLE IF EXISTS TABLE `demo_table_mem`;\n\nCREATE TABLE `demo_table_mem` (\n`id` INT(20) NOT NULL AUTO_INCREMENT PRIMARY KEY,\n`first_name` VARCHAR(50) NOT NULL DEFAULT \u2018\u2019,\n`last_name` VARCHAR(50) NOT NULL DEFAULT \u2018\u2019,\n`email` VARCHAR(50) NOT NULL DEFAULT \u2018\u2019,\n`gender` VARCHAR(50) NOT NULL DEFAULT \u2018\u2019,\n`ip` VARCHAR(50) NOT NULL DEFAULT \u2018\u2019\n) ENGINE = MEMORY; -- < add this\n\nCREATE INDEX `hash_idx` ON `demo_table_mem`(`first_name`) USING HASH;<\/pre><\/div>\n\n\n\n
Now, execute the create statements again, and you will see a different output:<\/p>\n\n\n\n
\"D:\\Misc\\Redgate\\Blogs\\August<\/figure>\n\n\n\n

Image 3 \u2013 Defining a Hash Index on the MEMORY Storage Engine<\/strong><\/p>\n\n\n\n
If we inspect the memory-based table structure now, we will see that all of our indexes are hash indexes instead<\/em>:<\/p>\n\n\n\n
\"D:\\Misc\\Redgate\\Blogs\\August<\/figure>\n\n\n\n

Image 4 \u2013 Hash Indexes in MariaDB<\/strong><\/p>\n\n\n\n
Even the primary key index is a hash index! Interesting, right?<\/p>\n\n\n\n
That\u2019s because our table is based on the MEMORY<\/code> storage engine, and as all of its data is stored in memory, MySQL doesn\u2019t have to default to using B-tree indexes and can suggest (and choose) the faster option<\/em> \u2013 a hash-based index \u2013 instead.<\/p>\n\n\n\n
The same is true for clustered indexes \u2013 define an AUTO_INCREMENT<\/code> on an ID<\/code> column on a memory-based storage engine, and you will have a hash-based clustered index, too \u2013 it will be hyper-fast to its clustered nature and hash-based structure. Cool, right?<\/p>\n\n\n\n
\"D:\\Misc\\Redgate\\Blogs\\2024\\August<\/figure>\n\n\n\n
<\/p>\n\n\n\n
Image 5 \u2013 Creating an AUTO_INCREMENT Column Using the MEMORY Storage Engine<\/strong><\/p>\n\n\n\n
\"D:\\Misc\\Redgate\\Blogs\\2024\\August<\/figure>\n\n\n\n

 Image 6 \u2013 the AUTO_INCREMENT Column on a MEMORY Table is a HASH Index<\/strong><\/p>\n\n\n\n
Now, we will insert some data into our table and review how such indexes work.<\/p>\n\n\n\n
Hash Indexes in Action<\/h2>\n\n\n\n
First, let\u2019s insert some data into our in-memory-based table \u2013 for that, we can make use of ordinary INSERT<\/code> or LOAD DATA INFILE<\/code> statements without inventing anything revolutionary either (you can see the details for loading the data in the Appendix):<\/p>\n\n\n\n
\"D:\\Misc\\Redgate\\Blogs\\August<\/figure>\n\n\n\n

Image 7 – LOAD DATA INFILE Into a MEMORY Table<\/strong><\/p>\n\n\n\n
Everything works as usual \u2013 COUNT(*)<\/code> queries too:<\/p>\n\n\n\n
\"D:\\Misc\\Redgate\\Blogs\\August<\/figure>\n\n\n\n

Image 8 – COUNT(*) On a MEMORY Table<\/strong><\/p>\n\n\n\n
One thing I would direct your attention to is the time taken by MariaDB to complete each query \u2013 the first query was completed in 0.01 sec., and the second one in 0.002 sec., and that\u2019s not because we heavily optimized our database, but because our data resides in memory and can be accessed much faster. This can be confirmed once we run some basic tests on an InnoDB table and one running the MEMORY<\/code> storage engine.<\/p>\n\n\n\n
First, note that the same table (the structure is the same, too) will occupy space in memory the same way it occupies space on the disk,<\/em> meaning that, likely, you won\u2019t be able to store as much data in memory as you would be able to store on the disk. The MEMORY<\/code> storage engine has some more caveats, too:<\/p>\n\n\n
\n
    \n
  1. MEMORY<\/code> tables are similar to MyISAM tables in that they perform full-table locks when performing INSERT<\/code>, UPDATE<\/code>, and DELETE<\/code> operations. X amount of these operations means X amount of full table locks per second.<\/em><\/li>\n\n\n\n
  2. MEMORY<\/code> tables still incur disk I\/O: the MySQL daemon will contact the .frm file belonging to the MEMORY<\/code> table to verify that the table exists before parsing an SQL query. In other words, if you run many SQL queries against a MEMORY<\/code> table, you can notice some disk I\/O. <\/li>\n<\/ol>\n<\/div>\n\n\n
    These caveats are discussed in a StackOverflow thread by a former Pythian employee<\/a>, and they do have an impact on how you work with your database and the operations performed within. One of those operations may be a comparison of the storage engines (InnoDB<\/code> vs. MEMORY<\/code>):<\/p>\n\n\n\n
    SELECT * \nFROM `users_innodb`\nWHERE `email` = \u2018sample@sample.com\u2019;<\/pre><\/div>\n\n\n\n
    \"D:\\Misc\\Redgate\\Blogs\\2024\\August<\/figure>\n\n\n\n

    Image 9 \u2013 InnoDB Table Example<\/strong><\/p>\n\n\n\n
    SELECT * \nFROM `users_memory`\nWHERE `email` = \u2018sample@sample.com\u2019;<\/pre><\/div>\n\n\n\n
    \"D:\\Misc\\Redgate\\Blogs\\2024\\August<\/figure>\n\n\n\n

    Image 10 \u2013 MEMORY Table Example<\/strong><\/p>\n\n\n\n
    Here, the difference may be negligible because we only have 5,000 rows we run our tests on; once the row count in our tables increases, the results will become more and more visible. However, at the same time, the amount of space taken on the memory will increase accordingly \u2013 chances are that you don\u2019t have more than 64GB of RAM available, so tread that line carefully.<\/p>\n\n\n\n
    To understand these results, consider this illustration of hash indexes:<\/p>\n\n\n\n
    \"\"<\/figure>\n\n\n\n
    Image 11 \u2013 Basic Illustration of Hash Indexes in MySQL<\/strong><\/p>\n\n\n\n
    Since hash indexes use a hash function to map keys to an index position (see illustration above), such an algorithm becomes very useful when searching for specific data. Let\u2019s try hash indexes out \u2013 we will do that with 7 queries, which will search for different things using the equality, less than, more than, or all of those operators. We will:<\/p>\n\n\n
    \n
      \n
    1.  Search for an ID<\/code> value using the \u201cmore than\u201d operator. (><\/code>) <\/li>\n\n\n\n
    2.  Search for an ID<\/code> value using the \u201cequal or more than\u201d operator. (>=<\/code>) <\/li>\n\n\n\n
    3.  Search for a string value with an exact match. (=<\/code>) <\/li>\n\n\n\n
    4.  Search for two exact string values using an OR operator. <\/li>\n\n\n\n
    5.  Utilize a LIKE<\/code> query that uses an index (we won\u2019t have a wildcard in front of the query.) <\/li>\n\n\n\n
    6.  Search for an ID<\/code> value using the NULL<\/code> safe equality operator. (<=><\/code>) <\/li>\n\n\n\n
    7.  Run a search using the \u201cless than or equal to\u201d operator. (<=<\/code>) <\/li>\n<\/ol>\n<\/div>\n\n\n
      Our results will look like so:<\/p>\n\n\n\n
      \"D:\\Misc\\Redgate\\Blogs\\August<\/figure>\n\n\n\n

      Image 12 \u2013 EXPLAINing HASH Indexes in MariaDB<\/strong><\/p>\n\n\n\n
      \"D:\\Misc\\Redgate\\Blogs\\August<\/figure>\n\n\n\n

      Image 13 \u2013 EXPLAINing HASH Indexes in MariaDB Part 2<\/strong><\/p>\n\n\n\n
      Attentive readers will notice that the internal functionality of hash indexes is somewhat different than that of B-trees. From the examples above, we can notice that, unlike B-tree indexes that can be used together with the =<\/code>, <<\/code>, >=<\/code>, <<\/code>, <=<\/code> or BETWEEN<\/code> operators, hash-based indexes can only be considered for use if we search for an exact match together with the <\/em>=<\/code> or <\/em><=><\/code> operators.<\/em><\/p>\n\n\n\n
      In other words, hash indexes are useless if we are searching for more than a single value because our database won\u2019t even consider such an index for use in the first place. That happens because hash indexes are used to improve data access speed when employing them in a single-value lookup operation and not anything else \u2013 as the core premise of such indexes is data residing in memory, they don\u2019t have the luxury of assisting searches for a range of values or anything in that realm, and that\u2019s why queries using clauses like BETWEEN<\/code>, LIKE<\/code>, ><\/code>, or <<\/code> are out of the question. They won\u2019t make use of a hash index, no matter how your query is written.<\/p>\n\n\n\n
      To find a row using a hash index, your database will calculate the hash value of the value you\u2019re searching for, then perform a search of the buckets of values that hashed to that same value. If it finds a result in the bucket, the result is returned.<\/p>\n\n\n\n
      The examples above support all hash index characteristics defined in the documentation of MySQL<\/a>.<\/p>\n\n\n\n
      MySQL\u2019s Adaptive Hash Index<\/h2>\n\n\n\n
      Many users of MySQL will also be aware of an index called the adaptive hash index. The InnoDB adaptive hash index acts as a feature assisting in-memory hash lookups for indexing. InnoDB builds the adaptive hash index at runtime, and such an index adapts to your workload in the sense that if it \u201csenses\u201d that a particular value is being looked up more often, it creates an entry in itself either with the full value or its prefix, depending on your use case. Then, if such a value is searched for once again, MySQL will then use the Adaptive Hash Index instead of a B-tree index.<\/em><\/p>\n\n\n\n
      Such an index can be enabled by fiddling with the innodb_adaptive_hash_index variable<\/a> (available values include 0 and 1), and it can be used to \u201cadd a layer\u201d between MySQL and the InnoDB buffer pool. Then, the entire structure of your database would look like this:<\/p>\n\n\n\n
      \"\"<\/figure>\n\n\n\n
      Image 14 \u2013 The Adaptive Hash Index in Between the InnoDB Buffer Pool and the Disk<\/strong><\/p>\n\n\n\n
      In MySQL, MariaDB, and Percona Server, the adaptive hash index is enabled by default and will be built automatically if InnoDB deems it to be necessary:<\/p>\n\n\n\n
      \"D:\\Misc\\Redgate\\Blogs\\August<\/figure>\n\n\n\n

      Image 15 – An Adaptive Hash Index is Enabled by Default<\/strong><\/p>\n\n\n\n
      Coming back to hash indexes, though, understand that a hash index works by taking the keys of the indexed values and \u201chashing them\u201d into output values. As such, and as I\u2019ve already mentioned numerous times in this blog post alone, hash indexes should only be used if we\u2019re 100% certain that we will only ever need a column to be looked up with equality operators and not with anything else.<\/p>\n\n\n\n
      To recap, consider a hash index when:<\/p>\n\n\n
      \n