Home >Backend Development >PHP Tutorial >MYSQL index types: PRIMARY, INDEX, UNIQUE, FULLTEXT, SPAIAL What are the differences? What occasions is each suitable for? , fulltextspaial_PHP tutorial

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

WBOY
WBOYOriginal
2016-07-13 09:46:251028browse

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

www.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...
Statement:
The content of this article is voluntarily contributed by netizens, and the copyright belongs to the original author. This site does not assume corresponding legal responsibility. If you find any content suspected of plagiarism or infringement, please contact admin@php.cn