Mysql indexes

Indexes use various datastructures to store data to prevent full scans of all rows in the table.

Index Types

BTree

A binary tree. Default for persisted storage engines.

Optimize Queries that are

• sorted
• match on field prefixes (but not suffixes)
• range bound (less than 10, between C and F)

But not queries

• match on field suffixes

Hash

A hash table. Default for the memory engine.
hash-collisions are accounted for, but each key with the same hash will need to be checked, making the query more expensive.
Indexes have a small memory footprint.

Optimize queries that are

• based on the full value (ex. IN, NOT IN, =)

But not queries

• you can't match on only a single key from a multi-key index (since both keys are hashed together)
• hash-tables are un-ordered, so range queries are not optimized (ex. less than 10, betweeen C and F)