Using full-text indexing in MySQL (fulltext index)

Using full-text indexing in MySQL (fulltext index)

A useful feature of MySQL is the ability to find text using full-text indexing (fulltext index). Currently only valid when using the MyISAM type table (MyISAM is the default table type, and if you don't know what type of table is used, it's probably MyISAM). Full-text indexes can be built on text,char or varchar-type fields, or field combinations. We will create a simple table to explain the various features.
The Simple Usage (MATCH () function) is valid for later versions of 3.23.23, and the complex usage (in BOOLEAN mode modifier) is valid for versions later than 4, the first part of this article focuses on simple usage, and the second part speaks of complex usage.
a simple table
We will use the following table throughout the process.
CREATE TABLE fulltext_sample(copy TEXT,FULLTEXT(copy)) TYPE=MyISAM;
If you do not set the default table type to a type other than MyISAM then Type=myisam can be omitted. After the table is built, some data is populated to it, for example:
INSERT INTO fulltext_sample VALUES
(‘It appears good from here‘),
(‘The here and the past‘),
(‘Why are we hear‘),
(‘An all-out alert‘),
(‘All you need is love‘),
(‘A good alert‘);
If you have created a table, you can add a full-text index using ALTER TABLE (just like the CREATE INDEX statement) statement, for example:
ALTER TABLE fulltext_sample ADD FULLTEXT(copy)
Find Text
The syntax for full-text index searches is simple, you just need the match field, against the text you're looking for, for example:
mysql> SELECT * FROM fulltext_sample WHERE MATCH(copy) AGAINST(‘love‘);
+----------------------+
| copy |
+----------------------+
| All you need is love |
+----------------------+
Searching on a full-text index is case-insensitive, so the following statement can work as well:
mysql> SELECT * FROM fulltext_sample WHERE MATCH(copy) AGAINST(‘LOVE‘);
+----------------------+
| copy |
+----------------------+
| All you need is love |
+----------------------+
Full-text indexing is often used to search for natural language text, such as newspaper articles, Web content, and so on. MySQL adds a lot of features to this type of search. mysql does not index any text that is less than or equal to 3, and does not index words that have a 50% chance of appearing. This means that if your table is less than 2 records, Search based on full-text indexing does not return anything. In the future, MySQL will make this feature more flexible, but now it should be suitable for most natural language use. If most of your records in your database contain "music", you probably don't want to return those records, you can use the in The BOOLEAN mode modifier obtains about 50% of the threshold, see the second part of this article.
The results are returned in the order in which the correlations are high.
Key Features
The following are the main features of the standard full-text index search:
1. Exclude duplicate words
2. Exclude words with a length of less than 4
3. Exclude words that appear in more than half of the records (i.e. as long as 3 records are needed)
4. Words with hyphens are considered to be two words
5. Results are returned in descending order of relevance
6. The words in the Ignore list are also excluded from the search results. The Ignore list is based on common English words, so if your data is used for different purposes, you may want to change the Ignore list. Unfortunately, this is not easy. You need to edit the file myisam/ft_static.c, Re-edit MySQL and rebuild the index! Here is an ignore list. Note that these are changed in different versions.
Ignore list
"A", "a", "Able", "about", "above", "according", "accordingly", "across", "actually", "after", "afterwards", "Again", "a Gainst "," Ain ' t "," All "," Allow "," allows "," almost "," Alone "," along "," already "," also "," although "," Always "," AM "," AM Ong "," amongst "," an "," and "," another "," any "," anybody "," anyhow "," anyone "," anything "," anyway "," anyways "," Anywhere " , "Apart", "appear", "appreciate", "appropriate", "is", "aren ' t", "Around", "as", "aside", "ask", "asking", "associated", "At", "available", "Away", "awfully", "B", "Be", "became", "because", "become", "becomes", "becoming", "been", "before", "Beforehand", "behind", "being", "Believe", "below", "beside", "besides", "Best", "better", "between", "beyond", "both", " Brief "," but "," by "," C "," C ' Mon "," C ' s "," came "," can "," can ' t "," cannot "," cant "," cause "," causes "," certain "," certain Ly "," Changes "," clearly "," co "," com "," Come "," comes "," concerning "," consequently "," consider "," considering "," Contai N "," containing "," ContaiNS "," corresponding "," could "," couldn ' t "," course "," currently "," D "," definitely "," described "," despite "," did "," didn ' t ', ' different ', ' do ', ' does ', ' doesn ' t, ' doing ', ' don ', ' did ', ' down ', ' downwards ', ' during ', ' e ', ' Each ', ' edu ', ' E G "," eight "," either "," else "," elsewhere "," enough "," entirely "," especially "," et "," etc "," even "," ever "," every "," Eve Rybody "," Everyone "," everything "," everywhere "," ex "," exactly "," example "," except "," F "," far "," few "," fifth "," first " , "Five", "followed", "following", "follows", "for", "former", "formerly", "Forth", "four", "from", "further", "Furthermor E "," G "," get "," gets "," getting "," given "," gives "," Go "," goes "," going "," Gone "," got "," gotten "," Greetings "," H "," Ha D "," Hadn ' t "," happens "," hardly "," has "," hasn ' t "," had "," Haven ' t "," having "," he "," he's "," Hello "," help "," hence "," Her "Here", "Here's", "Hereafter", "hereby", "herein", "Hereupon", "hers", "Herself", "hi", "him", "himself", "his", "Hi Ther "," hopefully "," how ", "Howbeit", "however", "I", "I-D", "I-ll", "I-M", "I-ve", "ie", "if", "ignored", "immediate", "in", "Inasmuch", "Inc", "I Ndeed "," indicate "," indicated "," indicates "," inner "," insofar "," instead "," into "," inward "," is "," isn ' t "," it "," it ' d " , "It ' ll", "It's", "it", "itself", "J", "just", "K", "Keep", "keeps", "kept", "know", "knows", "known", "L", "Last", "lat Ely "," later "," latter "," latterly "," least "," less "," lest "," let "," Let's "," like "," liked "," likely "," little "," look ", "Looking", "looks", "Ltd", "M", "mainly", "many", "may", "maybe", "Me", "mean", "meanwhile", "merely", "might", "more", " Moreover "," most "," mostly "," much "," must "," my "," Myself "," N "," name "," namely "," nd "," near "," nearly "," necessary "," Need "," needs "," neither "," never "," nevertheless "," new "," Next "," Nine "," No "," Nobody "," Non "," none "," Noone "," nor ", ' Normally ', ' not ', ' nothing ', ' novel ', ' Now ', ' Nowhere ', ' o ', ' obviously ', ' of ', ' off ', ' often ', ' oh ', ' OK ', ' okay ', ' old ' , "On", "Once", "one", "Ones ', ' only ', ' onto ', ' or ', ' other ', ' others ', ' otherwise ', ' ought ', ' our ', ' ours ', ' ourselves ', ' out ', ' outside ', ' over ' , "Overall", "own", "P", "particular", "particularly", "per", "perhaps", "placed", "please", "plus", "possible", "presumab Ly "," probably "," provides "," Q "," que "," quite "," QV "," R "," rather "," rd "," re "," really "," reasonably "," regarding "," R Egardless "," regards "," relatively "," respectively "," right "," s "," said "," Same "," saw "," say "," saying "," says "," second "," Secondly "," see "," seeing "," seem "," seemed "," seeming "," seems "," seen "," self "," selves "," sensible "," sent "," Serio US "," seriously "," seven "," several "," shall "," she "," should "," shouldn ' t "," since "," six "," so "," some "," Somebody "," so Mehow "," Someone "," something "," sometime "," sometimes "," somewhat "," Somewhere "," soon "," sorry "," specified "," Specify "," Specifying "," Still "," sub "," Such "," Sup "," sure "," T "," T ' s "," take "," Taken "," Tell "," tends "," th "," than "," thank "," Thanks "," thanx "," thaT "," That's "," Thats "," the "," their "," theirs "," them "," themselves "," then "," thence "," there "," there ' "," thereafter ", "Thereby", "therefore", "therein", "theres", "thereupon", "these", "they", "they ' d", "they ' ll", "they ' re", "they ' ve", "th Ink "," third "," This "," thorough "," thoroughly "," those "," though "," three "," through "," throughout "," thru "," Thus "," to " , "Together", "too", "took", "toward", "towards", "tried", "tries", "truly", "try", "trying", "twice", "I", "U", "un", " Under "," unfortunately "," unless "," unlikely "," until "," unto "," Up "," upon "," us "," use "," used "," useful "," uses "," usin G "," usually "," V "," value "," various "," very "," via "," viz "," vs "," W "," Want "," wants "," was "," wasn ' t "," is "," we "," W " E ' d "," We'll "," we ' re "," we ' ve "," Welcome "," Well "," went "," were "," weren ' t "," what "," what "," whatever "," where "," when Ce "," whenever "," where "," where "," whereafter "," whereas "," whereby "," wherein "," whereupon "," wherever "," whether "," Which "," while "," Whither"Who", "Who's", "whoever", "whole", "whom", "whose", "Why", "would", "willing", "Wish", "with", "within", "without", "Wo N ' t "," Wonder "," would "," would "," wouldn ' t "," X "," Y "," yes "," yet "," You "," You ' d "," You'll "," You ' re "," You VE "," your " , "Yours", "Yourself", "yourselves", "Z", "Zero",
Let's take a look at some of these words. If you're lazy, but want to find the word "love," like this:
mysql> SELECT * FROM fulltext_sample WHERE MATCH(copy) AGAINST(‘lov‘);
Empty set (0.00 sec)
Nothing is returned because the full-text index contains only the complete word, not part of the word. If you want to return, you must write the word complete, as in the first example.
As we mentioned, hyphenation words are excluded from the full-text index (they are indexed as separate words), so the following statement doesn't return anything:
mysql> SELECT * FROM fulltext_sample WHERE MATCH(copy) AGAINST(‘all-out‘);
Empty set (0.00 sec)
Unfortunately, two words are less than 4 characters, so they do not appear on a separate search, and do not appear in the usual search. The second part of this article uses a Boolean mode search to search for parts of words that contain hyphens.
You can also search for more than one word at a time, separated by commas. The following example finds records that contain "Here" and "appears":
mysql> SELECT * FROM fulltext_sample WHERE MATCH(copy) AGAINST(‘here‘,‘appears‘);
Empty set (0.01 sec)
Unexpectedly this statement did not return. But look at the Ignore list, the word is listed, and therefore excluded from the index. The Ignore list may be the usual reason why the MySQL full-text index is not in effect. If your query returns a result, your version of MySQL's ignore list does not contain " Here's the word.
Correlation degree
The following example shows the priority of the record return
mysql> SELECT * FROM fulltext_sample WHERE MATCH(copy) AGAINST(‘good,alert‘);
+---------------------------+
| copy |
+---------------------------+
| A good alert |
| It appears good from here |
| An all-out alert |
+---------------------------+
Record "A good alert" first appears because it contains two words to search. You don't have to trust me.-Just look at the priority that MySQL displays in the results. Simply repeat the match () function in the field list, for example:
mysql> SELECT copy,MATCH(copy) AGAINST(‘good,alert‘) AS relevance
FROM fulltext_sample WHERE MATCH(copy) AGAINST(‘good,alert‘);
+---------------------------+------------------+
| copy | relevance |
+---------------------------+------------------+
| A good alert | 1.3551264824316 |
| An all-out alert | 0.68526663197496 |
| It appears good from hear | 0.67003110026735 |
+---------------------------+------------------+
The correlation degree is very complex, based on the number of words in the index, the number of different words in the record, the total number of words in the index and the returned results, and the importance of the word. This number may be different in your MySQL version, and MySQL occasionally reinforces the computational logic.
The standard full-text index search is useful and sufficient for most applications, and MySQL 4 makes it even more powerful.

Using full-text indexing in MySQL (fulltext index)