MYSQL index types: PRIMARY, INDEX, UNIQUE, FULLTEXT, SPAIAL What are the differences? What occasions is each suitable for? , fulltextspaial_PHP tutorial

What are the differences between MYSQL index types: PRIMARY, INDEX, UNIQUE, FULLTEXT, SPAIAL? What occasions is each suitable for? , fulltextspaial

Mysql common indexes are: primary key index, unique index, ordinary index, full-text index, combined 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` )

The difference between various Mysql indexes:

Normal index (INDEX): the most basic index, without any restrictions
Unique index (UNIQUE): similar to "normal index", the difference is: index Column values ​​must be unique, but null values ​​are allowed.
Primary key index (PRIMARY): It is a special unique index that does not allow null values.
Full-text index (FULLTEXT): It can only be used for MyISAM tables. It is used to retrieve text information in an article. For larger data, it is very time-consuming and space-consuming to generate a full-text index.
Combined index: In order to further improve the efficiency of mysql, a composite index can be established, following the "leftmost prefix" principle.

For example , let’s say you are making a membership card system for a shopping mall.
This system has a membership table
with the following fields:
Member number INT
Member Name VARCHAR(10)
Member ID number VARCHAR(18)
Member phone number VARCHAR(10)
Member address VARCHAR( 50)
Member remark information TEXT

Then this member number, as the primary key, uses PRIMARY
member name. If you want to build an index, then it is an ordinary INDEX
member ID card If you want to build an index for the number, you can choose UNIQUE (unique, no duplicates allowed)
Member’s remarks information. If you need to build an index, you can choose FULLTEXT for full-text search.

However, FULLTEXT works best when used to search for a long article.
is used for relatively short text. If it is only one or two lines, ordinary INDEX can also be used.

Create index: CREATE UNIQUE INDEX indexName ON tableName(tableColumns(length))

Syntax to delete index: DROP INDEX index_name ON tableName

http://www.bkjia.com/PHPjc/1033240.htmlwww.bkjia.comtruehttp: //www.bkjia.com/PHPjc/1033240.htmlTechArticleMYSQL index types: PRIMARY, INDEX, UNIQUE, FULLTEXT, SPAIAL What are the differences? What occasions is each suitable for? , fulltextspaial Mysql common indexes are: primary key index, unique index, ordinary...