Mysql index Type normal, unique, fulltext

Question 1: What are the differences between mysql index types normal, unique, and fulltext? Normal: Indicates normal index unique: Indicates unique and duplicate indexes are not allowed. If the field information is not repeated, for example, when the ID card number is used as an index, you can set it to uniquefulltextl: indicates the full-text search index. FULLTEXT is used for long search

Question 1: What are the differences between mysql index types normal, unique, and full text? Normal: Indicates normal index unique: Indicates unique and duplicate indexes are not allowed. If the field information is not repeated, for example, when the ID card number is used as an index, you can set it to unique full textl: indicates the full-text search index. FULLTEXT is used for long search

Question 1: What are the differences between mysql index types normal, unique, and full text?

Normal: indicates a normal index.

Unique: unique, and duplicate indexes are not allowed. If the field information is not repeated, for example, when the ID card number is used as an index, you can set it to unique.

Full textl: full-text search index. When FULLTEXT is used to search for a long article, it works best. It is used in short text. If there are only one or two lines of text, the normal INDEX is also acceptable.

In summary, the index category is determined by the content characteristics of the index field. Normally, normal is the most common.

Question 2: Which fields in the table should be selected as indexes during actual operations?

To make indexes more efficient, you must consider which fields to create indexes and what types of indexes to create when creating indexes. There are seven principles:

1. Select a unique index
2. Create an index for fields that often require sorting, grouping, and Union operations
3. Create an index for fields that are often used as query Conditions
4. Limit the number of Indexes
5. Try to use indexes with a small amount of data
6. Try to use the prefix for indexing.
7. Delete indexes that are no longer in use or rarely used

I. MySQL: indexes are saved in B-tree format.

The Memory storage engine can select Hash or BTree indexes. Hash indexes can only be used for Equality comparison of = or <=>.

1. Normal index: create index on Tablename (column list)

Alter table TableName add index (column list)

Create table TableName ([...], index [IndexName] (column list)

2. unique index: create unique index

Alter... add unique

Primary key: A unique index, which must be specified as the primary key.

3. Full-text indexing: Full-text indexing and full-text retrieval are supported from version 3.23.23. FULLTEXT,

It can be created on char, varchar, or text columns.

4. Single-Column and multi-column indexes:

The query results of Multiple Single-Column indexes are different from those of a Single-Column index, because:

When performing a query, MySQL can only use one index and selects the most restrictive index from Multiple indexes.

5. Leftmost prefix (Leftmost Prefixing): Multiple-column index, for example, fname_lname_age index. MySQL will use the following search conditions:

Fname_lname_age index: firstname, lastname, age; firstname, lastname; firstname, which is not used in other cases.

2. determine which type of index to create Based on the SQL query statement and how to optimize the query

Select an index column:

A. In the performance optimization process, selecting which column to create an index is one of the most important steps. Which of the following indexes can be used?

Two types of columns: columns that appear in the where clause and columns that appear in the join clause.

B. Considering the distribution of values in a column, the larger the base of the index column, the better the index effect.

C. Use short indexes. If a string column is indexed, you should specify a prefix length to save a lot of index space and increase the query speed.

D. Use the leftmost prefix

E. Do not over-index the required indexes. Each additional index occupies additional disk space and reduces write performance.

When modifying the table content, the index must be updated and may sometimes need to be reconstructed. Therefore, the more indexes, the longer the time it takes.

MySQL uses indexes only for the following operators: <, <=, =,>,> =, between, in,

And sometimes like (not starting with a wildcard "%" or ).

Mysql Index classification

Index fields in database tables can greatly improve the query speed. By making good use of these indexes, you can make MySQL query and operation more efficient. Index is the key to quick search. The establishment of MySQL indexes is very important for the efficient operation of MySQL. The following describes several common MySQL index types.
1. Common Indexes
This is the most basic index type, and it has no limitations such as uniqueness. Common indexes can be created in the following ways:
(1) CREATE an INDEX, for example, create index name ON tablename (column name 1, column name 2 ,...);
(2) modify a TABLE, for example, alter table tablename add index name (column name 1, column name 2 ,...);
(3) specify an INDEX when creating a TABLE, such as create table tablename ([...], INDEX name (column name 1, column name)
2 ,...));
2. Unique Index
This index is basically the same as the previous "normal index", but there is a difference: all values of the index column can only appear once, that is, they must be unique. You can create a unique index in the following ways:
(1) CREATE an INDEX, for example, create unique index name ON tablename (column list );
(2) modify a TABLE, for example, alter table tablename add unique index name (column list );
(3) specify an index when creating a TABLE, for example, create table tablename ([...], UNIQUE index name (column Column
Table ));
3. Primary Key
A primary key is a unique index, but it must be specified as a "primary key ". If you have used columns of the AUTO_INCREMENT type, you may already be familiar with primary keys and other concepts. The primary key is generally specified during TABLE creation, for example, "create table tablename ([...], primary key (column list ));". However, we can also ADD a primary key by modifying the TABLE, for example, "alter table tablename add primary key (column list );". Each table can have only one primary key. (The primary key is equivalent to an aggregate index, which is the fastest search index)
4. Single-Column and multi-column Indexes
An index can be a single-column index or multiple-column index.
(1) A single column index is a common index of a column field.
(2) A multi-column index is an index containing multiple column fields.
Alter table student add index sy (name, age, score );
Index sy is a multi-column index. Multi-column indexes are valid only in the following situations:
Select * from student where name = 'jia 'and age> = '12' // The first column field and
Second Field
Select * from student where name = 'jia '// The where condition contains only the first column of fields
Select * from student where name = 'jia 'and score <60 // The where condition contains the first column field and the third word.
Segment
Conclusion: Multi-column indexes are valid only when the where condition contains the first column field in the index.
5. Select an index Column
How to select an index column depends on the query conditions first. Generally, the columns in the query conditions are used as indexes.