SQL Use Index principle

Source: Internet
Author: User
Tags mysql index

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

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.