This is the 24th day of my participation in the August More Text Challenge

There are five types of Mysql indexes:

INDEX: a basic INDEX with no restrictions. UNIQUE: The INDEX column must have UNIQUE values, but empty values are allowed. PRIMARY: A special unique index that does not allow null values. FULLTEXT index (FULLTEXT) : can be used for MyISAM tables, mysql5.6 can also be used for innodb tables, used for retrieving text information in an article, for large data, the generation of FULLTEXT index is time-consuming and space. Join (composite) indexes: To improve mysql efficiency, you can create composite indexes, following the “leftmost prefix” principle.

Take a look at the full-text index.

Full-text index, usually used not much, more words of the article query basically we use Chinese word segmentation to segmentation query, rather than the use of full-text index to query. Full-text indexing is still inefficient.

However, compared with the data with less text, the efficiency of using full-text index is higher than that of using like for fuzzy query.

One: Basic use

1: create a full-text index when creating a table

create table fulltext_test ( id int(11) NOT NULL AUTO_INCREMENT, content text NOT NULL, tag varchar(255), MyISAM DEFAULT CHARSET=utf8; MyISAM DEFAULT CHARSET=utf8;Copy the code

2: Create full-text indexes on existing tables

(1) : create index with create statement

create fulltext index content_tag_fulltext
    on fulltext_test(content,tag);
Copy the code

(2) : create index with ALTER statement

alter table fulltext_test
    add fulltext index content_tag_fulltext(content,tag);
Copy the code

3: Modify the full-text index

Modify the full text index is a bit of trouble, want to modify the words, directly delete to create a good.

4: Deletes the full-text index

(1) : DROP full-text INDEX directly using DROP INDEX

drop index content_tag_fulltext
    on fulltext_test;
Copy the code

(2) : Delete full-text index by SQL statement ALTER TABLE

alter table fulltext_test
    drop index content_tag_fulltext;
Copy the code

5: Use full-text indexes

Grammar:

MATCH (columnName) AGAINST ('string')
Copy the code

Example:

SELECT * FROM `student` WHERE MATCH(`name`) AGAINST('hearing')
 
SQL > select * from multiple columns;
It is recommended to create a federated full-text index on this multi-column data, otherwise the index will not be used.
 
SELECT * FROM `student` WHERE MATCH(`name`,`address`) AGAINST('Cong Guang Dong')
Copy the code

Note: The columns specified in the match() function must be exactly 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 the full-text index does not record which column the keyword came from. If you want to use a full-text index for a column, create a separate full-text index for that column.

Two: test full-text indexes

Create table;

create table test (
    id int(11) unsigned not null auto_increment,
    content text not null.primary key(id),
    fulltext key content_index(content)
) engine=MyISAM default charset=utf8;
Copy the code

Write several pieces of data:

insert into test (content) values ('a'), ('b'), ('c');
insert into test (content) values ('aa'), ('bb'), ('cc');
insert into test (content) values ('aaa'), ('bbb'), ('ccc');
insert into test (content) values ('aaaa'), ('bbbb'), ('cccc');
Copy the code

View the data in the data table:

MariaDB [test]> select * from test;
+----+---------+
| id | content |
+----+---------+
|  1 | a       |
|  2 | b       |
|  3 | c       |
|  4 | aa      |
|  5 | bb      |
|  6 | cc      |
|  7 | aaa     |
|  8 | bbb     |
|  9 | ccc     |
| 10 | aaaa    |
| 11 | bbbb    |
| 12 | cccc    |
+----+---------+
12 rows in set (0.00 sec)
Copy the code

We now use full-text indexes to query the data in the table: Note the difference in the result set of my query statement:

MariaDB [test]> select * from test where match(content) against('a');
Empty set (0.01 sec)
 
MariaDB [test]> select * from test where match(content) against('aa');
Empty set (0.00 sec)
 
MariaDB [test]> select * from test where match(content) against('aaa');
Empty set (0.00 sec)
 
MariaDB [test]> select * from test where match(content) against('aaaa');
+----+---------+
| id | content |
+----+---------+
| 10 | aaaa    |
+----+---------+
1 row in set (0.00 sec)
 
Copy the code

Select * from test where match(content) against(‘aaaa’); Data was found, but no data was found for the other three SQL statements.

This is because mysql full-text indexes are limited by minimum and maximum search lengths.

3. Minimum search length and maximum search length of full-text index

MySQL full-text indexes have two variables, minimum search length and maximum search length. Words with a length less than the minimum search length and a length greater than the maximum search length are not indexed. In plain English, to use a full-text index search for a term, the length of the term must be within the range of the above two variables.

1: Run the following command to view the configuration of the current database:

show

 variables 
like
 
'%ft%'
;
Copy the code

You can see the names and default values of these two variables in both MyISAM and InnoDB storage engines

// 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

As you can see, the default minimum search length is 4 for MyISAM and 3 for InnoDB. In other words, MySQL’s full-text index only indexes words with length greater than or equal to 4 or 3.

2: Sets the minimum search length

You need to modify the mysql configuration file.

The usual mysql configuration file is in /etc/my.cnf. Append the following:

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

Then restart the MySQL server. Then, you need to fix the existing full-text index, otherwise, the changes just made will not take effect.

My advice here is, don’t fix it, just delete and rebuild it.

Four: two full-text indexes

1: Full-text index of natural language

By default, or when using the in Natural Language mode modifier, the match() function performs a natural language search on the text collection, and the examples above are natural language full-text indexes.

The natural language search engine will calculate the relevance of each document object and query. Here, relevance is based on the number of matched keywords and the number of times that keyword appears in the document. The fewer times words appear in the overall index, the higher their relevance. Conversely, very common words will not be searched, and if a word appears in more than 50% of the records, natural language searches will not search for such words. As mentioned above, there must be more than four records in the test table for this reason.

This mechanism is also easier to understand, for example, a data table storage is a piece of article, the common words in the article, modal words and so on, there must be more, it is not meaningful to search these words, need to search those articles have special meaning of the word, so as to separate the article.

2: Boolean full-text index

In Boolean search, we can customize the relevance of a particular term being searched in the query, and when writing a Boolean search query, we can customize the search with some prefix modifiers.

Ft_boolean_syntax = ft_boolean_syntax = ft_boolean_syntax = ft_boolean_syntax

1: + Must contain the word

2: – Must not contain the word

3: Improve the relevance of the word, the results of the query is earlier

4: The correlation of the word is reduced, and the query result is later

5: (*) asterisk wildcard, can only be followed by a word

The problem mentioned above can be solved by using a Boolean full-text index query. Using the following command, a, AA, aaa, and aAAA are all queried.

select * test where match(content) against(‘a*’ in boolean mode);

5: pay attention to

1: make clear version support before using full-text index;

2: Full-text index is N times faster than like + %, but there may be precision problems;

3: If a large amount of data needs to be indexed, you are advised to add the data before creating the index.

4: For Chinese, you can use MySQL after 5.7.6, or third-party plug-ins.

This is about all the content of the full-text index.

For good suggestions, please enter your comments below.

Welcome to my blog guanchao.site

Welcome to applets: