- 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.
FULLTEXTare stored in B+trees.
- spatial data types use R-trees;
MEMORYtables also support hash indexes; choose B-tree for
MEMORYtables if some queries use range operators.
- InnoDB uses inverted lists for
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