,

MySQL:How index works on each storage engine

Contents


Types of indexes

B-Tree

  • This is the 'normal' index
  • It stores the indexed columns in the indexed order: indexing 'first_name','last_name' is different than 'last_name','first_name'
  • Stores columns in alphabetical order and sequence so if you are searching for all records which the column 'field_name' is between I and K, it's efficient
  • Create indexes with the same columns but in different order

Goo matches

    • leftmost prefix
    • full index value
    • match a column prefix
    • a range of values
    • one part exact match and range on another part

Bad matches

  • If the lookup is for the second indexed column before the first, it doesn't help
  • You can't skip columns

Hash

  • Supported only by memory storage engine
  • it can be emulated using CRC32() in a string column which converts it into an int

Spacial index (R-Tree)

  • Supported by MyISAM only
  • It's like B-Tree but the index follow any order not just left to right
  • But for this to work you must use MySQL GIS functions such as MBRCONTAINS()

Full text index

  • Supported by MyISAM only
  • It doesn't work with the WHERE clause, but the MATCH AGAINST


Index layout for each storage engine

  • MyISAM
    • It stores the rows in the order they are inserted. If the row-size is fixed, it finds the rows quicker if the correct indexes ere placed.it makes easy to build an index as well.
    • Non-clustered storage engine
    • There is no difference between primary key index and other indexes.
    • It stores data and indexes separately
  • It stores one id per index and that will make the table grow exponentially and every row moved will have to have it's pointer changed as well. It consumes server and needs optimization to reorder theses ids.
  • InnoDB
  • It's clustered, so it doesn't store the rows and the index separately
  • It uses the primary id as a pointer, that's why doesn't need to optimize

Clustered index

They are part the hole table, differently from the secondary indexes which are non-clustered. Remember that only solidDB and InnoDB have it.

Covering indexes

It's when it returns the index value PLUS another value from an indexed column. If says int he EXPLAIN 'Using index', it's a covering indexation