Differences between INDEX PRIMARY UNIQUE FULLTEXT in MySQL

Question

What are the differences between PRIMARY  UNIQUE  INDEX and FULLTEXT when creating MySQL tables   How would I use them

User · Answer

All of these are kinds of indices   primary  must be unique  is an index  is  likely  the physical index  can be only one per table   unique  as it says  You 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  a more specialized form of indexing that allows full text search  Think of it as  essentially  creating an  index  for each  word  in the specified column

User · Answer

I feel like this has been well covered  maybe except for the following   Simple KEY   INDEX  or otherwise called SECONDARY INDEX  do increase performance if selectivity is sufficient  On this matter  the usual recommendation is that if the amount of records in the result set on which an index is applied exceeds 20  of the total amount of records of the parent table  then the index will be ineffective  In practice each architecture will differ but  the idea is still correct   Secondary Indexes  and that is very specific to mysql  should not be seen as completely separate and different objects from the primary key  In fact  both should be used jointly and  once this information known  provide an additional tool to the mysql DBA  in Mysql  indexes embed the primary key  It leads to significant performance improvements  specifically when cleverly building implicit covering indexes such as described there   If you feel like your data should be UNIQUE  use a unique index  You may think it s optional  for instance  working it out at application level  and that a normal index will do  but it actually represents a guarantee for Mysql that each row is unique  which incidentally provides a performance benefit   You can only use FULLTEXT  or otherwise called SEARCH INDEX  with Innodb  In MySQL 5 6 4 and up  and Myisam Engines  You can only use FULLTEXT on CHAR  VARCHAR and TEXT column types  FULLTEXT index involves a LOT more than just creating an index  There s a bunch of system tables created  a completely separate caching system and some specific rules and optimizations applied  See http   dev mysql com doc refman 5 7 en fulltext-restrictions html and http   dev mysql com doc refman 5 7 en innodb-fulltext-index html

User · Answer

Differences  KEY or INDEX refers to a normal non-unique index   Non-distinct values for the index are allowed  so the index may contain rows with identical values in all columns of the index   These indexes don t enforce any restraints on your data so they are used only for access - for quickly reaching certain ranges of records without scanning all records   UNIQUE refers to an index where all rows of the index must be unique   That is  the same row may not have identical non-NULL values for all columns in this index as another row   As well as being used to quickly reach certain record ranges  UNIQUE indexes can be used to enforce restraints on data  because the database system does not allow the distinct values rule to be broken when inserting or updating data  Your database system may allow a UNIQUE index to be applied to columns which allow NULL values  in which case two rows are allowed to be identical if they both contain a NULL value  the rationale here is that NULL is considered not equal to itself    Depending on your application  however  you may find this undesirable  if you wish to prevent this  you should disallow NULL values in the relevant columns   PRIMARY acts exactly like a UNIQUE index  except that it is always named  PRIMARY   and there may be only one on a table  and there should always be one  though some database systems don t enforce this    A PRIMARY index is intended as a primary means to uniquely identify any row in the table  so unlike UNIQUE it should not be used on any columns which allow NULL values   Your PRIMARY index should be on the smallest number of columns that are sufficient to uniquely identify a row   Often  this is just one column containing a unique auto-incremented number  but if there is anything else that can uniquely identify a row  such as  quot countrycode quot  in a list of countries  you can use that instead  Some database systems  such as MySQL s InnoDB  will store a table s records on disk in the order in which they appear in the PRIMARY index   FULLTEXT indexes are different from all of the above  and their behaviour differs significantly between database systems   FULLTEXT indexes are only useful for full text searches done with the MATCH     AGAINST   clause  unlike the above three - which are typically implemented internally using b-trees  allowing for selecting  sorting or ranges starting from left most column  or hash tables  allowing for selection starting from left most column   Where the other index types are general-purpose  a FULLTEXT index is specialised  in that it serves a narrow purpose  it s only used for a  quot full text search quot  feature    Similarities  All of these indexes may have more than one column in them   With the exception of FULLTEXT  the column order is significant  for the index to be useful in a query  the query must use columns from the index starting from the left - it can t use just the second  third or fourth part of an index  unless it is also using the previous columns in the index to match static values    For a FULLTEXT index to be useful to a query  the query must use all columns of the index

[mysql] Differences between INDEX, PRIMARY, UNIQUE, FULLTEXT in MySQL?

Examples related to mysql

Examples related to indexing

Examples related to key

Examples related to schema