This is my sixth day of the August Challenge

Quote:

When the DB executes an Sql statement, the DB performs a full table scan based on the search criteria by default, and adds the table that matches the search criteria to the search result set. If we add an index to a field, the query will first go to the number of rows in the index list at a time to locate the specific value, greatly reducing the number of rows traversed to match, so it can significantly increase the speed of the query.

An index is a data structure that helps MySQL obtain data efficiently. An index is a data structure

Mysql indexes are divided into three categories: single-column indexes (common indexes, unique indexes, primary key indexes), composite indexes, and full-text indexes.

1. Single-column index

An index contains only a single column, but a table can have multiple single-column indexes

1. Common index: A basic index type in Mysql. There are no restrictions.

2, unique index: The value in the index column must be unique, and can be null

3. Primary key index: a special unique index that cannot be null

Grammar:

Create index directly:

create index index_name on table (column_name); Create a common index.Copy the code
Create unique index index_name on table (column_name); Creating a unique indexCopy the code

The primary key index needs to be created when the table is created or the table structure is modified

Delete index:

drop index index_name on table_name; Delete index (delete unique index)

2. Composite Index (Composite index)

An index created on a combination of columns in a table will be used only if the left side of those fields is used in the query. When using a combination index, it follows the leftmost prefix set

Create index index_name on table_name(column_1,column_2,column_3.....)Copy the code

For easy understanding, you can interpret the preceding indexes as (column_1),(Column_1, COLUMN_2), and (Column_1, COLUMN_2,column_3). The indexes must start from the left. If the breakpoint for column_1 and column_3 occurs, Then column_1 takes effect.

Third, full-text index

Support:
  • Before MySQL 5.6, only MyISAM storage engine supported full-text indexing;
  • MySQL 5.6 and later, MyISAM and InnoDB

All storage engines support full-text indexes;

  • A full-text index can be created only for fields whose data types are CHAR, VARCHar, TEXT, or their series.
Create:

create fulltext index index_name on table_name (column_1,column_2….) ;

Delete:

With other indexes

Use:

Full-text indexes have their own syntax format, using the match and Against keywords, such as

select * from fulltext_test where match(column_1,column_2) against('xxx xxx');
Copy the code

Note: The columns specified in the match() function must be the same as the columns specified in the full-text index, otherwise an error will be reported and the full-text index cannot be used, because full-text indexes do not record which columns the keywords came from. If you want to use a full-text index for a column, create a full-text index for that column alone

The minimum length of the search keyword, the variable name and the default value in MyISAM and InnoDB. If it is lower than or higher than the default value, the search will be null

// MyISAM
ft_min_word_len = 4;
ft_max_word_len = 84;

// InnoDB
innodb_ft_min_token_size = 3;
innodb_ft_max_token_size = 84;
Copy the code
Set the minimum search length

The parameters related to the full-text index cannot be dynamically modified. You must modify the MySQL configuration file to complete the modification. To change the minimum search length to 1, open the MySQL configuration file /etc/my.cnf and append the following content to [mysqld]

[mysqld]
innodb_ft_min_token_size = 1
ft_min_word_len = 1
Copy the code

Then restart the MySQL server and fix the full-text index. Note that after modifying the parameter, make sure to fix the index, otherwise the parameter will not take effect.

repair table test quick;
Copy the code