Index Types

  • primary: must be unique, is an index, is (likely) the physical index, can be only one per table.
  • unique: can't have more than one row with a tuple of this value. Note that since a unique key can be over more than one column, this doesn't necessarily mean that each individual column in the index is unique, but that each combination of values across these columns is unique.
  • index: if it's not primary or unique, it doesn't constrain values inserted into the table, but it does allow them to be looked up more efficiently.
  • fulltext: allows full text search. Essentially creating an "index" for each "word" in the specified column.

Data Structure:

  • PRIMARY KEY, UNIQUE, INDEX, and FULLTEXT are stored in B+trees.
  • spatial data types use R-trees;
  • MEMORY tables also support hash indexes; choose B-tree for MEMORY tables if some queries use range operators.
  • InnoDB uses inverted lists for FULLTEXT indexes.


MySQL is moving to x509 for authentication, away from username + pw


Install from apt-get:

$ sudo apt-get install mysql-server

Start mysql command line:

$ mysql -u root -p