TechPrufer
Get started
PlanetScale
PlanetScale logoPlanetScale · Feature trail

MySQL Adaptive Hash Index

1 post · Apr 2024

What this trail covers

PlanetScale's managed MySQL offering now includes automatic optimization of B-tree index lookups through the Adaptive Hash Index (AHI). This feature dynamically builds in-memory hash indexes based on observed query patterns, accelerating frequently accessed data retrieval. The AHI operates on top of the InnoDB buffer pool, and its effectiveness is dependent on sufficient buffer pool size. Users can enable or disable this feature via the `innodb_adaptive_hash_index` configuration option.

All PlanetScale trails
  1. The MySQL adaptive hash indexThis post introduces and explains the MySQL Adaptive Hash Index (AHI) feature. It details how AHI works by creating in-memory hash indexes on top of B-tree indexes to speed up repeated lookups. The post covers the underlying concepts of B-trees and the InnoDB buffer pool, contrasts hash lookups with B-tree lookups, and explains how AHI dynamically adapts to workload characteristics. It also includes a performance test demonstrating a 16% speedup by enabling AHI for a repeated query. The post notes that AHI is typically enabled by default but can be disabled.