What Is a MEMORY Hash Indexes in MySQL?
The MEMORY engine uses hash indexes by default and also supports B-tree indexes. Hash buckets locate exact keys without preserving sorted value order.
How the MEMORY Hash Indexes Algorithm Works
The MEMORY engine uses hash indexes by default and also supports B-tree indexes. Hash buckets locate exact keys without preserving sorted value order.
When to Use MEMORY Hash Indexes
Small in-memory tables needing equality lookup when MEMORY volatility and limits are acceptable.
How to Create a MySQL MEMORY Hash Indexes
Review data quality, existing structures, feature support, build time, and deployment requirements before creating an index on a production table.
CREATE TABLE session_lookup (token CHAR(32) NOT NULL, user_id BIGINT NOT NULL, KEY token_hash (token) USING HASH) ENGINE=MEMORY;MEMORY Hash Indexes Query Example
This example demonstrates the matching query shape. Check the execution plan to confirm that the engine can use the index.
SELECT user_id FROM session_lookup WHERE token = 'ab12cd34';Queries Supported by MEMORY Hash Indexes
Small in-memory tables needing equality lookup when MEMORY volatility and limits are acceptable. A similar-looking predicate may not use the same access path; operators, expressions, key order, and data type all matter.
Read Performance, Storage, and Write Costs
Hash cannot serve ranges or ordering; MEMORY data is volatile across restart and bounded by memory. Indexes reduce some search work but require storage and add maintenance to writes.
MEMORY Hash Indexes Limitations and Common Mistakes
Do not confuse MEMORY hash indexes with InnoDB B-tree indexes or its adaptive hash optimization.
Do not assume that an index being present means the optimizer chose it or that the query became selective.
How to Measure and Tune MEMORY Hash Indexes
Confirm table engine and index with SHOW INDEX; benchmark size, restart expectations, and equality load.
Compare representative plans and timings before and after, including concurrent writes and realistic data volumes.
MEMORY Hash Indexes Questions
Will the database always use this index?
No. The optimizer weighs selectivity, estimated costs, statistics, cache state, and the amount of data returned.
What should I verify before production?
Confirm version support, query compatibility, write overhead, build/rollback procedures, and index behavior during backup and recovery.
Official MySQL Index Documentation
Check MySQL 8.4 Reference Manual: Optimization and Indexes for release-specific requirements and supported operators.