What are the index types in mysql

The index types in mysql are: 1. Ordinary index; 2. Unique index; 3. Primary key index; 4. Combined index; 5. Full-text index. A normal index is the most basic index and has no restrictions. A unique index requires that the value of the index column must be unique, but null values ​​are allowed.

The index types in MySQL are as follows

• Normal index

• Unique index

• Primary key index

• ##Combined index

• Full-text index

mysql video tutorial)

1. Ordinary index

Create index directly

CREATE INDEX index_name ON table(column(length))

Add index by modifying table structure

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

Create an index at the same time 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 ,    PRIMARY KEY (`id`),    INDEX index_name (title(length))
)

Delete the index

DROP INDEX index_name ON table

2. Unique indexSimilar to the previous ordinary index, the difference is 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:

Create a unique index

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

Modify the table structure

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

Create When specifying a table, directly specify

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. Composite index refers to the index created on multiple fields. Only the created index is used in the query conditions. When the first field, the index will be used. 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 comparing with values ​​in the index. 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.

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

Modify the table structure and add a full-text index

ALTER TABLE article ADD FULLTEXT index_content(content)

Create directly Index

CREATE FULLTEXT INDEX index_content ON article(content)

Disadvantages

2. Creating an index file will occupy disk space. Generally, this problem is not serious, but if you create multiple combined indexes on a large table, the index file will grow quickly. Indexes are only one factor that improves efficiency. If you have a table with a large amount of data, you need to spend time researching and building the best index or optimizing query statements.

Notes

SELECT * FROM table_name WHERE YEAR(column_name)<2017;

The above is the detailed content of What are the index types in mysql. For more information, please follow other related articles on the PHP Chinese website!