Mysql indexes

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

Index Types

Index

A regular index, on a single column. Defaults to BTREE.

Multi-Column Index

A multi-column index is a regular index,
where the values of multiple columns are concatenated together to form one string.

The order of the columns is significant,
but there are debates over the optimal ordering.

- (specific -> unspecfic) will create a larger (on disk) tree,
  since there will be duplicates of the unspecific entries

- (unspecific -> specific) TBD

Index Datastructure Types

Show the current index using

SHOW INDEX FROM foo_table;

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 that

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