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)

FULLTEXT

Fulltext indexes are optimized for finding keywords within blocks of text.
They are used when the MATCH AGAINST ".." operator is used.
It may still be beneficial to have a second index with a different type on the same column.