MySQL index type: PRIMARY, what's the difference between index,unique,fulltext,spaial? What are the applicable occasions? , fulltextspaial_php Tutorial

Source: Internet
Author: User
Tags mysql index

MySQL index type: PRIMARY, what's the difference between index,unique,fulltext,spaial? What are the applicable occasions? , fulltextspaial


MySQL Common indexes are: Primary key index, unique index, normal index, full-text index, composite index
PRIMARY key (primary key index) ALTER TABLE ' table_name ' Add PRIMARY KEY (' column ') unique (unique index) ALTER table ' table_name ' Add unique (' column ')
Index (normal index) ALTER TABLE ' table_name ' Add INDEX index_name (' column ') fulltext (full-text index) ALTER table ' table_name ' Add fulltext (' column ') )
Combined Index ALTER TABLE ' table_name ' ADD INDEX index_name (' column1 ', ' column2 ', ' column3 ')


MySQL various index differences:


Normal index: The most basic index, without any restrictions
Unique index (unique): Similar to "normal index", the difference is that the value of the indexed column must be unique, but allow a null value.
Primary key index (PRIMARY): It is a special unique index and is not allowed to have null values.
Full-text index (FULLTEXT): Available only for MyISAM tables, for retrieving textual information in an article, generating a full-text index for large data is a time-consuming space.
Combined index: For more MySQL efficiency, you can create a composite index that follows the "leftmost prefix" principle.

For example , for example, you are making a membership card system for a mall.
This system has a membership table
The following fields are available:
Member ID INT
Member name VARCHAR (10)
Member ID number VARCHAR (18)
Member Phone VARCHAR (10)
Member Address VARCHAR (50)
Member Note Information TEXT

Then this membership number, as the primary key, using PRIMARY
Member name if you want to index, then it is the normal index
Member ID number if you want to index, then you can choose unique (unique, not allowed to repeat)
Member notes information, if need to build index, you can choose Fulltext, full-text search.

Fulltext, however, works best when it comes to searching for a long post.
Used in relatively short text, if the one or two lines of the word, the normal INDEX can also.

Creating an index: Create UNIQUE index indexname on tableName (tablecolumns (length))

Syntax for dropping an index: Drop index index_name on TableName

http://www.bkjia.com/PHPjc/1033240.html www.bkjia.com true http://www.bkjia.com/PHPjc/1033240.html techarticle MySQL index type: PRIMARY, what's the difference between index,unique,fulltext,spaial? What are the applicable occasions? , fulltextspaial MySQL Common indexes are: Primary key index, unique index, normal ...

  • 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.