Mysql full-text index learning notes

Full-text indexing is a special feature in mysql. Next I will introduce some notes about mysql full-text indexing. If you have any questions, please refer to it.

Scenario: You need to perform a fuzzy query on the title, but there are a lot of records and must be relatively accurate, such as search: ac, abc is not allowed, acb, bac is acceptable, and so on.

Mysql full-text search has three modes:

1. Natural language search. This is the default full-text search method for mysql. SQL example:
[Code = plain]

The Code is as follows:	Copy code
Select id, title FROM post where match (content) AGAINST ('search keyword ')

Or explicitly declare the use of natural language search
[Code = plain]

The Code is as follows:	Copy code
Select id, title FROM post where match (content) AGAINST ('search keyword' in natural language mode)

Test:

1. 1 million data, mysql/mongo, in this case. No matter what data is queried, it is basically seconds,
Mysql query is like '% xxxx %', mongo is {title:/xxxx/I}
In general, the speed of the two is almost the same, but if you query keywords that do not exist in the database, they are generally about 0.2 seconds to 2 seconds, mongo will be relatively better, at 0.5 seconds
 
2. 5 million ~ 10 million data
The query conditions are as follows:
Mysql queries occupy about 40% of the cpu, more than 20 seconds (mysql 11 million data)
About 50% of the CPU used for mongo query, about 10 S/8 S (about 5.5 million of mongo)
This performance cannot be used.
 
--- Next step
1. xunsearch/coreseek (sphworkflow)
2. mysql full-text index
 
You need to test it again. Although mysql 1 million only takes about 0.00x or 0.0x seconds. However, if there are multiple concurrent threads, they will become stuck.
Therefore, we need to consider the complexity of the scenario again.

After yesterday's processing, I made a new Note. This note is purely a personal test and is related to actual conditions. For example, the field I want to query cannot exceed 255 of the length of varchar, that's why I did this.

After I made a general index yesterday, I had 11 million records with an index of 220 MB. After I changed it to a full-text index, the index file was 1.1 GB. The storage space increased by about 5 times.

The following are notes. Please do not joke. The scenario is different.
 

• Tested

• After the title field is changed to full-text index
• Advantages:
• The speed is also 0.0x seconds. Fast
• Even if there is an or condition and the limit parameter is included, the speed is very fast.
• Disadvantages:
• If the query does not contain limit, it will be stuck because it calculates total count.
• Select count () is stuck
• If no keyword exists in the query, the query will be stuck.
• Usage
• Do not perform select count queries whenever possible (when the number is less than 1 million, you can consider that when the number is greater than 1 million, it is not necessary)
• The query must contain the limit condition.
• When no keyword is found, record it to the keyword database. When a new record is added, select the keyword database. If a keyword exists in the new room, remove the keyword to avoid getting stuck.
• Third-party tools such as coreseek (sphink) and xunsearch are not used for the time being
• Xunsearch only supports word segmentation and does not support full match.
• Third-party tools consume memory and are not timely enough for incremental operations

Bill says a large article goes to the test below

Although this method is feasible,

However, for full-text search, this is an extremely inefficient method, especially when processing a large amount of data.

-------------------
The above sentence I saw on the Internet makes sense. mysql itself provides a technology called full-text search,

However, this seems to have been available only in later versions. Older versions are not supported, but it is an earlier version,

Currently, all versions are supported. I am using mysql6.0.4 for demonstration now

I think full-text search is more comprehensive than index search. The index only applies to a certain field, and then uses like in combination during query.

For full-text search, you can set multiple fields for search, which is more advanced than select... like.

Well, since full-text search has such advantages, let's see if it is true.

The test example provided below is an example in the mysql manual.

The Code is as follows:	Copy code
Create table articles ( Id int unsigned AUTO_INCREMENT not null primary key, Title VARCHAR (200 ), Body TEXT, FULLTEXT (title, body) );

This is the mysql statement used to create a table. The last FULLTEXT (title, body)

Creates a full-text search for the title and body.

Body of the title. Copy the statement to create a table.
Check whether full-text search is created. The following statement is used to view the table's primary key, index, and full-text search.

Show indexes from Table Name

The Code is as follows:

Copy code

Mysql> show indexes from articles; + ---------- + ------------ + ---------- + -------------- + ------------- + | Table | Non_unique | Key_name | Seq_in_index | Column_name | + ---------- + ------------ + ---------- + -------------- + ------------- + | Articles | 0 | PRIMARY | 1 | id | | Articles | 1 | title | 1 | title | | Articles | 1 | title | 2 | body | + ---------- + ------------ + ---------- + -------------- + ------------- + 3 rows in set (0.01 sec)

We can see that the Key_name is successfully created, and its field column name Column_name is title and body.

========================

Add data content to the table below so that we can test it.

The Code is as follows:

Copy code

Insert into articles (title, body) VALUES ('Mysql Tutorial ', 'dbms stands for DataBase ...'), ('How To Use MySQL well', 'after you went through ...'), ('Optimizing mysql', 'In this tutorial we will show ...'), ('1970 MySQL Tricks ', '1. Never run mysqld as root. 2 ....'), ('Mysql vs. yoursql', 'In the following database comparison ...'), ('Mysql security', 'When configured properly, MySQL ...'); Mysql> select * from articles; + ---- + ----------------------- + -------------------------------------------- + | Id | title | body | + ---- + ----------------------- + -------------------------------------------- + | 1 | MySQL Tutorial | DBMS stands for DataBase... | | 2 | How To Use MySQL Well | After you went through a... | | 3 | Optimizing MySQL | In this tutorial we will show... | | 4 | 1001 MySQL Tricks | 1. Never run mysqld as root. 2... | | 5 | MySQL vs. YourSQL | In the following database comparison... | | 6 | MySQL Security | When configured properly, MySQL... | + ---- + ----------------------- + -------------------------------------------- + 6 rows in set (0.00 sec)

After the database is added, we will use the query statements provided by full-text search for testing.

========================================================== ==========

The statement template is as follows:

SELECT Table field FROM table name where match (full-text search table field) AGAINST ('search string ');

Search for the title and body containing the database string below

The Code is as follows:

Copy code

Mysql> SELECT * FROM articles -> -> Where match (title, body) AGAINST ('database '); + ---- + ------------------- + -------------------------------------------- + | Id | title | body | + ---- + ------------------- + -------------------------------------------- + | 5 | MySQL vs. YourSQL | In the following database comparison... | | 1 | MySQL Tutorial | DBMS stands for DataBase... | + ---- + ------------------- + -------------------------------------------- + 2 rows in set (0.00 sec)

MATCH is equivalent to the column to be searched, and AGAINST is the content to be searched.

It's a little different than like.

In addition, match... against also provides many operations to further filter data,

Generally, it can be used as a more accurate search,

For example, in the following example, the search title and body contain MySQL, but the results of YourSQL cannot be found.

The Code is as follows:	Copy code
SELECT * FROM articles where match (title, body) AGAINST ('+ MySQL-yoursql' in boolean mode ); ------------------ | 5 | MySQL vs. YourSQL | In the following database comparison... |

You will find that this result is filtered out.

Fulltext also provides more logic searches, that is, some fuzzy searches, and so on, with higher requirements.
You can search for matching characters on the official mysql website.

========================================================== ==================================

The above full-text search is created when the table is created. If you have already created a table, but want to add
To use this function, you can use the following statement:

The Code is as follows:	Copy code
Mysql> alter table articles add fulltext index (title, body );

--------------------

For demonstration, I will delete the previous one first.

The Code is as follows:	Copy code
Mysql> drop index title on articles;

Check that the file has been deleted.

The Code is as follows:	Copy code
Mysql> show indexes from articles;

Add fulltext to the table.

The Code is as follows:	Copy code
Mysql> alter table articles add fulltext index (title, body );

Now, modify the existing table to add the full-text search function. I hope this tutorial will help you.
This search function will be used in the future.