what is the difference between mysql indexes

Differences: 1. The index column value of the unique index must be unique, and null values ​​are allowed; 2. The index value of the primary key index must be unique, but null values ​​are not allowed; 3. The combined index can only be used in the query conditions The index will be used only when the first field used when creating the index is used; 4. Full-text indexes 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:

The difference between primary key index: and unique index is that no null values ​​are allowed. When creating a primary key This index is created automatically.

Normal index: The most basic index, no special restrictions.

Unique index: The difference from an ordinary index is that the value of the index column must be unique, but it can have null values.

Full-text index: Can only be used in tables using the Myisam storage engine, for larger data columns.

Combined index: refers to an index created on multiple fields. The index will be used only if the first field when creating the index is used in the query conditions. When using combined indexes, follow the leftmost prefix set

. Examples are as follows:

1. Ordinary index

is the most basic index. There are no restrictions. It has the following creation methods:

(1) Directly create an index

CREATE INDEX index_name ON table(column(length))

(2) Modify the table structure and add an index

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

(3) Create a table Create index

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))
)

at the same time (4) Delete index

DROP INDEX index_name ON table

2. Unique index

is similar to the previous ordinary index, except that: The value of the index column must be unique, but null values ​​are allowed. In the case of a composite index, the combination of column values ​​must be unique. It has the following creation methods:

(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) Directly specify # 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. A table can only have one primary key and no null values ​​are allowed. Generally, the primary key index is created at the same time when creating the table:

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

4. Combined index

refers to the index created on multiple fields and can only be used in query conditions. The index will be used only if the first field is specified when creating the index. When using combined indexes, follow the leftmost prefix set

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

5. Full-text index

is mainly used to find keywords in the text, rather than directly matching the values ​​​​in the index Compared. The fulltext index is very different from other indexes. It is more like a search engine rather than a simple parameter matching of the where statement. The fulltext index is used with the match against operation instead of the general where statement 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, varchar, and text columns. It is worth mentioning that when the amount of data is large, it is better to put the data into a table without a global index and then use CREATE index to create a fulltext index than to first create a fulltext for a table and then write the data. The speed is much faster.

(1) It is suitable to add a full-text index when creating 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 and add a full-text index

ALTER TABLE article ADD FULLTEXT index_content(content)

(3) Create an index directly

CREATE FULLTEXT INDEX index_content ON article(content)

Recommended learning:

mysql video tutorial

The above is the detailed content of what is the difference between mysql indexes. For more information, please follow other related articles on the PHP Chinese website!