Comparison of "MySQL" primary key, normal index, unique index, and full-text index

The Ysql index is used to quickly look for records with specific values, and all MySQL indexes are saved as B-trees. If there is no index, MySQL must start scanning all records of the entire table from the first record until it finds a record that meets the requirements. The higher the number of records in the table, the higher the cost of this operation. If an index has been created on a column that is a search condition, MySQL can quickly get to where the target record is without scanning any records. If a table has 1000 records, finding records by index is at least 100 times times faster than sequential scan records.

Can MySQL use the index in a like query? Where do you use the index?

When using like, if you use ' percent ', will you use the index?
EXPLAINSELECT * from ' user ' WHERE username like '%ptd_% '; The result above is a full table scan and is not used to the index. Just use a% query result: EXPLAIN SELECT * from ' user ' WHERE username like ' ptd_% '; this is used to the index. EXPLAIN SELECT * from ' user ' where username like '%ptd_ '; in front of use% is also full table scan, not used to index.In summary, MySQL uses a like query only when you use the following% to use the index.

General analysis

PRIMARY, INDEX, UNIQUE these 3 kinds are a kind of

PRIMARY primary key. is unique and cannot be empty.

Index, normal

Unique index. Duplicates are not allowed.

Fulltext is a full-text index that is used to retrieve textual information in an article.

MySQL index type: PRIMARY, what's the difference between index,unique,fulltext,spaial? What are the applicable occasions?

For example, for example, you are making a membership card system for a mall.
This system has a membership table
The following fields are available:
Member ID INT
Member name VARCHAR (10)
Member ID number VARCHAR (18)
Member Phone VARCHAR (10)
Member Address VARCHAR (50)
Member Note Information TEXT

Then this membership number, as the primary key, using PRIMARY
Member name if you want to index, then it is the normal index
Member ID number if you want to index, then you can choose unique (unique, not allowed to repeat)
Member notes information, if need to build index, you can choose Fulltext, full-text search.

Fulltext, however, works best when it comes to searching for a long post.
Used in relatively short text, if the one or two lines of the word, the normal INDEX can also.

Create INDEX usage Instructions

Normal index

This is the most basic type of index, and it has no limitations such as uniqueness.

Create indexes, such as the name of the CREATE INDEX < index > on tablename (List of columns);
Modify the table, such as ALTER TABLE TableName ADD index [name of index] (list of columns);
Specify an index when creating a table, such as CREATE TABLE TableName ([...], index [name of indexed] (List of columns));

Uniqueness Index

This index is basically the same as the previous "normal index", but there is one difference: all the values of an indexed column can only occur once, that is, they must be unique.

MySQL PRIMARY key:

The MySQL primary key is a unique index, but it must be specified as "PRIMARY key".

For example "CREATE TABLE tablename ([...], PRIMARY KEY (List of columns)"); ”。 However, we can also add the primary key by modifying the table, such as "ALTER table tablename Add PRIMARY key (List of columns); ”。 There can be only one primary key per table.

Full-Text indexing (for use with fuzzy queries)

MySQL supports full-text indexing and full-text retrieval starting from version 3.23.23. In MySQL, the index type of the full-text index is fulltext. a full-text index can be created on a column of char, varchar, or text type. it can be created by the CREATE TABLE command or by the ALTER TABLE or CREATE INDEX command. For large datasets, it is faster to create a full-text index by using the ALTER TABLE (or CREATE INDEX) command than to insert the record into an empty table with a full-text index.

How to use: (English example, Chinese complex; subsequent updates)

Statement Description: WHERE MATCH (column_name) against (' Key_word1,key_word2 ' in BOOLEAN MODE) index name: Fulltext support type: char, varchar, Text below in turn is the increment, delete, check full-text index example: ALTER TABLE Xxf_witkey_article ADD fulltext username (art_title);D ROP index username on Xxf_witkey_article; SELECT * from ' xxf_witkey_article ' WHERE MATCH (art_title) against (' KKKK ');

Single-column and multicolumn indexes

Create indexes such as the name of the Create UNIQUE Index < index > on tablename (List of columns);
Modify the table, such as ALTER TABLE TableName ADD UNIQUE [index name] (List of columns);
Specify indexes when creating tables, such as CREATE TABLE TableName ([...], UNIQUE [index name] (List of columns)
);

Comparison of "MySQL" primary key, normal index, unique index, and full-text index