MySQL index type

MySQL What is the difference between the index type normal,unique,full text?

Normal: Indicates normal index

Unique: Represents a unique, not allowed duplicate index, if the field information is guaranteed not to be duplicated such as a social security number used as an index, can be set to a unique

Full TEXTL: The index that represents the fulltext search. Fulltext works best when searching for a very long article. Used in relatively short text, if the one or two lines of the word, the normal INDEX can also.

In summary, the category of the index is determined by the indexed field content attribute, which is usually the most common.

MySQL Index method BTREE Hash Difference

MySQL's most commonly used index structure is btree(O (log (n))), but there are always situations where we want to use other types of indexes for better performance. Hash is one of the options, such as when we retrieve the user ID from the user name,

They are always a one-to-one relationship, the use of the operator only = only, if the use of hash as the index data structure, the time complexity can be reduced to O (1). Unfortunately, in the current MySQL version (5.6), hash only supports memory and NDB

Two engines, and our most commonly used InnoDB and MyISAM do not support the hash type index.

B-tree indexes can be used on comparison operators like =,>,>=,<,<= and between. It can also be used with the LIKE operator, as long as its query condition is a constant that does not begin with a wildcard character.

The index of the Hash type has some characteristics that differ from the above:

1. They can only be used for peer comparisons, such as = and <=> operators (but much faster). They cannot be used for range query conditions such as <. If the system only needs to use such a storage structure as "key-value pair", use hash type Index as much as possible.
2. The optimizer cannot use a hash index to speed up the order by operator. (Such indexes cannot be used to search for values in the next order)
3.mysql cannot determine how many data are in between two values (this requires using the range query operator to determine which index to use). If you convert a MyISAM table to a memory table that relies on a hash index, it may affect some of the statements (performance).
4. Only the full key can be used to search for one row of data. (If you use the B-tree index, any one of the key fragments can be used for lookups.) I think it might mean that a wildcard like operator would not work.

Sometimes the use of scripts to migrate data will encounter garbled problem, even if the table character set to UTF8 also useless, this time before the execution of SQL add a set names UTF8 can be.

MySQL index type