MySQL full-text indexing Learning notes detailed

Scene: Need to do a fuzzy query about the title, only a little more records, and need to be relatively accurate, such as search: AC, can not appear ABC, acceptable ACB,BAC, and so on.

MySQL Full-text search has three different modes:

One, natural language search. This is the MySQL default Full-text search method, 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 methods
[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 in the 0.00x second level,
MySQL query is like '%xxxx% ', MONGO is {title:/xxxx/i}
In general, both speed is really similar, but if the query does not exist in the database of keywords, generally in 0.2 seconds to 2 seconds or so, MONGO will be relatively good, in 0.5 seconds

2, 5 million ~1000 million data
Query conditions
MySQL query when the CPU accounted for about 40%, more than 20 seconds (MySQL 11 million data)
MONGO query when the CPU accounted for about 50%, 10 seconds/8 seconds (MONGO 5.5 million)
This kind of performance doesn't work.

---Next
1, Xunsearch/coreseek (Sphinx)
2. mysql Full-text indexing

Need to test again. Key MySQL Although 1 million is only 0.00x or 0.0x seconds or so. But if multiple concurrent time will be stuck to death.
So the complexity of the scenario needs to be considered again.

After yesterday's processing, new notes, this time the note is purely personal test, and the actual conditions, for example, I want to query the field does not exceed the length of varchar 255, so I do so.

Yesterday to do the normal index, 11 million records, index 220M, converted to full-text indexing, index files for 1.1G, storage space, up about 5 times times.

Here are the notes, please don't joke, the scenes are different.

• After testing

Title field is changed to full text index, at 11 million
Advantages
• The speed is also 0.0x second level. Very fast.
• Even if there is an OR condition, as long as with the limit parameter, the speed is very fast
Shortcomings
• If the query does not take limit, it will die directly because he wants to calculate total count
Select COUNT () card dead
• If the query does not exist in the keyword, the card dies
• How to use
• Try not to do select Count query (the number of less than 1 million can be considered, more than 1 million when, in fact, no longer necessary)
• Enquiries must be carried on limit conditions
• Every time the query to the nonexistent keyword, record to the keyword library, each time there is a new record, select the keyword library, if the new room has the keyword, the keyword will be removed, to avoid the death card
• Temporarily do not use Coreseek (Sphinx)/xunsearch and other third-party tools
Xunsearch only supports participle query and does not support exact match
• Third-party tools, memory consumption, and incremental, not timely

The fee says a big piece below enter test

A like statement in a select query to execute this query, although this method is feasible,

But for Full-text lookups, this is an extremely inefficient approach, especially when dealing with large amounts of data.

-------------------
The above sentence I saw on the internet, said quite reasonable, MySQL itself provides a kind of technology called Full-text Search,

However, this seems to be from the later version, compared to the older version does not support, but it is very early version,

The version that you use now should all be supported. I am now using mysql6.0.4 to demonstrate

Full-text search compared to the index I feel more comprehensive, index only to a certain field, and then in the query when using like fit.

Full-Text Search it can set multiple fields to search, which can be said to be compared to select ... like Advanced bar.

Well, now that the full text search has the advantage, let's see if this is really the case.

The test example provided below is an example of the MySQL manual above

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) );

Above this is the MySQL statement that created the table, where the last sentence fulltext (title,body)

is to create a full-text search for title and body, which is to facilitate the search for titles and

The contents of the header body. Copy the statement up to create a successful table.
To see if the Full-text search is created, the following statement is: View the primary key of the table, index, 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)

You can see the successful creation, Key_name name is title, its field column name column_name is title and body

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

Here's a table to add the data content so we could 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 ok ', ' after you went through a ... '), (' Optimizing MySQL ', ' in this tutorial we'll show ... '), (' 1001 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 | After you went through a ... | | 3 | Optimizing MySQL | In this tutorial we'll 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)

To add a good database, let's test it by using the query statement provided by Full-text search.

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

The templates for using statements are as follows:

The SELECT table field from tables name WHERE MATCH (full-Text search table field) against (' search string ');

The following search title and Body contains database this string

code is as follows

copy code

mysql> SELECT * from articles        ->   & nbsp 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 the column you're looking for, and against is what you're looking for.

It's a little different than like.

And match ... against also provides many operations to further filter the data,

Can generally be used as a more accurate search,

For example, the following example: Search title and Body contains MySQL, but can not have yoursql results.

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'll find that you can see that the results are filtered out.

Fulltext also provides more logical searches, that is, some fuzzy search, etc., requiring a higher
Match character search, you can see on the official MySQL website.

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

The above set up a Full-text search is created while creating a table, if you have already built the table, but want to join
This function, you can use the following statement

The code is as follows	Copy Code
Mysql> ALTER TABLE articles add Fulltext index (title,body);

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

To demonstrate, I'll just delete the

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

Look again, it has been deleted.

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

Add Fulltext to Table

The code is as follows	Copy Code
Mysql> ALTER TABLE articles add Fulltext index (title,body);

See, so by modifying the existing table to add Full-text search functionality, hopefully this tutorial will help you
Use this search function later.