Shulou(Shulou.com)05/31 Report--

This article introduces the relevant knowledge of "what are the differences between mysql indexes". In the operation of actual cases, many people will encounter such a dilemma, so let the editor lead you to learn how to deal with these situations. I hope you can read it carefully and be able to achieve something!

Differences: 1, the unique index of the index column value must be unique, allow null values; 2, the index value of the primary key index must be unique, but no null values are allowed; 3, the combined index only in the query conditions when the use of the first field when the index is created, the index will be used; 4, full-text index can only be used in tables using the Myisam storage engine.

The operating environment of this tutorial: windows10 system, mysql8.0.22 version, Dell G3 computer.

What is the difference between mysql indexes

Mysql index type:

Primary key index: the difference between a primary key index and a unique index is that null values are not allowed and this index is created automatically when the primary key is created.

General index: the most basic index, there are no special restrictions.

Unique index: the difference from a normal index is that the value of the index column must be unique, but there can be null values.

Full-text indexing: can only be used in tables that use the Myisam storage engine, for larger data columns.

Composite index: an index created on multiple fields, which is used only if the first field when the index was created is used in the query condition. Follow the leftmost prefix set when using a combined index

Examples are as follows:

1. General index

Is the most basic index, it does not have any restrictions. It can be created in the following ways:

(1) create an index directly

CREATE INDEX index_name ON table (column (length))

(2) add an index by modifying the table structure

ALTER TABLE table_name ADD INDEX index_name ON (column (length))

(3) create the index when you create the table

CREATE TABLE `table` (`id` int (11) NOT NULL AUTO_INCREMENT, `title` char (255) CHARACTER NOT NULL, `content` text CHARACTER NULL, `time` int (10) NULL DEFAULT NULL, PRIMARY KEY (`id`), INDEX index_name (title (length)

(4) Delete the index

DROP INDEX index_name ON table

two。 Unique index

Similar to the previous normal index, except that the value of the index column must be unique, but null values are allowed. If it is a combined index, the combination of column values must be unique. It can be created in the following ways:

(1) create a unique index

CREATE UNIQUE INDEX indexName ON table (column (length))

(2) modify the table structure

ALTER TABLE table_name ADD UNIQUE indexName ON (column (length))

(3) specify directly when creating the table

CREATE TABLE `table` (`id` int (11) NOT NULL AUTO_INCREMENT, `title` char (255) CHARACTER NOT NULL, `content` text CHARACTER NULL, `time` int (10) NULL DEFAULT NULL, UNIQUE indexName (title (length)

3. Primary key index

Is a special unique index where a table can have only one primary key and no null values are allowed. Typically, the primary key index is created at the same time as the table is created:

CREATE TABLE `table` (`id` int (11) NOT NULL AUTO_INCREMENT, `title` char (255) NOT NULL, PRIMARY KEY (`id`))

4. Combinatorial index

An index created on multiple fields, which is used only if the first field when the index was created is used in the query condition. Follow the leftmost prefix set when using a combined index

ALTER TABLE `table` ADD INDEX name_city_age (name,city,age)

5. Full-text index

It is mainly used to find keywords in the text, rather than comparing them directly to the values in the index. The fulltext index is very different from other indexes in that it is more like a search engine than a simple parameter match for a where statement. Fulltext indexes are used with match against operations, rather than normal where statements plus like. It can be used in create table,alter table and create index, but currently only full-text indexes can be created on char and varchar,text columns. It is worth mentioning that when the amount of data is large, it is much faster to put the data into a table without a global index, and then create a fulltext index with CREATE index than to create a fulltext for a table and then write the data.

(1) it is suitable to add a full-text index to create a table

CREATE TABLE `table` (`id` int (11) NOT NULL AUTO_INCREMENT, `title` char (255) CHARACTER NOT NULL, `content` text CHARACTER NULL, `time` int (10) NULL DEFAULT NULL, PRIMARY KEY (`id`), FULLTEXT (content))

(2) modify the table structure to add a full-text index

ALTER TABLE article ADD FULLTEXT index_content (content)

(3) create the index directly.

This is the end of CREATE FULLTEXT INDEX index_content ON article (content) "what are the differences between mysql indexes"? thank you for your reading. If you want to know more about the industry, you can follow the website, the editor will output more high-quality practical articles for you!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.