Question 1:mysql What is the difference between 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.
More Information reference: http://blog.sina.com.cn/s/blog_887d00920100wgf3.html
Question 2: In the actual operation, which fields in the table should be selected as the index?
To make indexes more efficient to use, when creating indexes, you must consider which fields to create indexes on and what types of indexes to create, there are 7 principles:
1. Select a Uniqueness Index
2. Index fields that often require sorting, grouping, and Union operations
3. Indexing a field that is frequently used as a query condition
4. Limit the number of indexes
5. Try to use an index with a small amount of data
6. Use prefixes as far as possible to index
7. Delete indexes that are no longer used or used infrequently
More Information reference: http://blog.sina.com.cn/s/blog_887d00920100wgf1.html
Advanced:
1. Understanding MySQL Indexing and optimization: http://www.cnblogs.com/hustcat/archive/2009/10/28/1591648.html
2. The data structure and principle behind the MySQL index http://dotnet.cnblogs.com/page/151329/
3. Data structures used in indexes B-tree http://kb.cnblogs.com/page/45712/
SQL Use Index principle