Mysql index types and basic usage examples

This article uses examples to illustrate the types and basic usage of MySQL indexes. Share with you for your reference, the details are as follows:

index

MySQL currently has the following main index types:

• Normal index
• Unique Index
• Primary key index
• Composite Index
• Full-text index

- General index

Is the most basic index, it has no restrictions.

CREATE INDEX IndexName ON `TableName`(`field name`(length))

- Unique index

Similar to the previous ordinary index, the difference is that the value of the index column must be unique, but null values ​​are allowed. If it is a composite index, the combination of column values ​​must be unique.

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

- Primary key index

It 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 as the table is created.

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

- Combined index

Refers to an index created on multiple fields. The index will be used only if the first field when the index is created is used in the query condition. Follow the leftmost prefix set when using a composite index

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

- Full text index

It is mainly used to find keywords in 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 match of a where statement. The fulltext index is used in conjunction with the match against operation, rather than the general where statement plus like. It can be used in create table, alter table, and create index, but currently only char, varchar, and text columns can be used to create full-text indexes. 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 index for a table first and then write the data.

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

Readers who are interested in more MySQL-related content can check out the following topics on this site: "Summary of MySQL Index Operation Skills", "Summary of MySQL Common Functions", "Summary of MySQL Log Operation Skills", "Summary of MySQL Transaction Operation Skills", "Summary of MySQL Stored Procedure Skills" and "Summary of MySQL Database Lock-Related Skills".

I hope this article will be helpful to everyone's MySQL database design.