mysql query to use the match () against () function problem

sponsored links
MySQL supports full text indexing and search capabilities. MySQL FULLTEXT index type in the full-text index. FULLTEXT indexes for MyISAM tables only; they can be CHAR, VARCHAR or TEXT column as part of the CREATE TABLE statement is created, or subsequently use the ALTER TABLE or CREATE INDEX added. For larger data sets, enter your data without a FULLTEXT index to the table, then create the index, its data entry faster than the speed of existing FULLTEXT index more quickly.

Full-text search with MATCH () function is executed together.

mysql> CREATE TABLE articles (
-> Title VARCHAR (200),
-> Body TEXT,
-> FULLTEXT (title, body)
Query OK, 0 rows affected (0.00 sec)

mysql> INSERT INTO articles (title, body) VALUES
-> ('MySQL Tutorial', 'DBMS stands for DataBase ...'),
-> ('How To Use MySQL Well', 'After you went through a ...'),
-> ('Optimizing MySQL', 'In this tutorial we will 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 ...');
Query OK, 6 rows affected (0.00 sec)
Records: 6 Duplicates: 0 Warnings: 0

mysql> SELECT * FROM articles
-> WHERE MATCH (title, body) AGAINST ('database' IN BOOLEAN MODE);
+----+-------------------+------------------------ ------------------+
| 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 () function to perform database for a string of natural language search. A database is a set of one or two included in the FULLTEXT the column. Search string as a AGAINST () the parameters are given. For each row in the table, MATCH () returns a correlation value, that is, search string and MATCH () column in the table specified in the text of the line between a similarity measure.

By default, the search is performed case-insensitive way. However, you can be indexed by using the binary sort out a case-sensitive full-text search. For example, you can use the latin1 character set to a column to sort the given latin1_bin for the full-text search is case-sensitive.

If the above examples, when MATCH () is used in a WHERE statement, the related non-negative floating point value. Zero correlation means that there is no similarity. Correlation calculation is based on the number of words in the line, the line number of the unique child, the total number of words in the database, and contains a special word file (line) number.

For natural language full-text search, requiring MATCH () function in the name of your table columns and some FULLTEXT index contains the same columns. Information for the above, attention, MATCH () function (title and full text) named in the columns and articles Miao Nei ULLTEXT the same columns in the index. To search for the title and text, respectively, should create FULLTEXT indexes for each column.

Basically, the above example shows how to use the correlation line in order to return gradually weak MATCH () function. The following example will show how to explicitly retrieve the associated values. The order of rows returned is uncertain, because the SELECT statement does not contain a WHERE or ORDER BY clause:

mysql> SELECT id, MATCH (title, body) AGAINST ('Tutorial')
-> FROM articles;
| Id | MATCH (title, body) AGAINST ('Tutorial' IN BOOLEAN MODE) |
| 1 | 0.65545833110809 |
| 2 | 0 |
| 3 | 0.66266459226608 |
| 4 | 0 |
| 5 | 0 |
| 6 | 0 |
6 rows in set (0.00 sec)
The following example is more complex. Asked to return the relevant value, while the line is fading by relevance sort order. To achieve this result, you should specify the two MATCH (): once in the SELECT list and once in the WHERE clause. This does not cause additional housekeeping, because the MySQL optimizer noticed two MATCH () call is the same, which will activate a full-text search code.

mysql> SELECT id, body, MATCH (title, body) AGAINST
-> ('Security implications of running MySQL as root') AS score
-> FROM articles WHERE MATCH (title, body) AGAINST
-> ('Security implications of running MySQL as root' IN BOOLEAN MODE);
+----+-------------------------------------+------ -----------+
| Id | body | score |
+----+-------------------------------------+------ -----------+
| 4 | 1. Never run mysqld as root. 2. ... | 1.5219271183014 |
| 6 | When configured properly, MySQL ... | 1.3114095926285 |
+----+-------------------------------------+------ -----------+
2 rows in set (0.00 sec)
Table with 2 rows (0.00 seconds)

MySQL FULLTEXT prototype implementation of any word character (letters, numbers and underscores in part) as a sequence of words. This sequence may also contain a single quote ('), but no more than one in a row. This means that aaa'bbb will be seen as a word, and aaa''bbb are considered two words. In the word before or after the single quotation marks will be removed FULLTEXT parser; 'aaa'bbb' will become aaa'bbb.

FULLTEXT analysis program by looking for certain delimiter to determine the starting and ending word positions, such as '' (space character),, (comma) and. (Period). If the word is not a separator to separate (for example, in Chinese), the FULLTEXT parser can not determine a word's starting position and end position. To be able to in such language to add words to the FULLTEXT index or other indexed terms, you must preprocess them so that by some such as "sort of arbitrary delimiter separated.

Some words in the full-text search will be ignored:

Any too short word will be ignored. Full-text search can find the default minimum word length of 4 characters.
Stop word in the word will be ignored. Disable the word is to have a "the" or "some" that are too common and are considered non-semantic word. There is a built-in stop word, but it can be user-defined list is rewritten.

Thesaurus and ask each correct word in the thesaurus and ask according to their importance in being measured. In this way, a number of documents in the word with a lower importance (and even many of the importance of the word zero), because in this particular semantic lexicon in its low value. Conversely, if the word is relatively rare, then it will get a higher importance. And the importance of the word are combined to be used to calculate the correlation of the line.

Large lexicon of the technology used with the optimal contract (in fact, it is carefully adjusted at this time). For small tables, word distribution does not adequately reflect their semantic value, and this model may sometimes produce bizarre results. For example, although the word "MySQL" appear in the article table for each row, but the word may not get any search results:

mysql> SELECT * FROM articles

-> WHERE MATCH (title, body) AGAINST ('MySQL');

Can not find the search term (0.00 seconds)

The search result is empty because the word "MySQL" appear in at least 50% of the full text of the line. Therefore, it is included in the stop word. For large data sets, using the most appropriate to the operation of a natural language inquiry ---- not from a 1GB table every line and return again. For small data sets, its usefulness may be relatively small.

A line with the contents of all rows in the table of half the words are less likely to find relevant documents. In fact, it is easier to find a lot of irrelevant content. We all know that when we try to use the Internet search engine to find information on when the high frequency of this happening. Can be inferred, because the line contains the word where the particular data set and was given a lower semantic value. A given word may be in a data set with more than 50% of its threshold, while in another data set is not.

When you first try to use full-text search to understand the working process, this 50% threshold provides important operational implication: If you create a table and only insert one row of articles 1, 2, and each text words appear in all lines of at least 50% probability. Then the result is what you do not search. Must be inserted at least 3 lines, and the more the better. Need to bypass the 50% limit of the user can use Boolean search code
  • StumbleUpon
  • Digg
  • TwitThis
  • Mixx
  • Technorati
  • Facebook
  • NewsVine
  • Reddit
  • Google
  • LinkedIn
  • YahooMyWeb

Related Posts of mysql query to use the match () against () function problem

  • Various Ruby virtual machine comparison

    At QCon San Francisco, the United States in 2008 a report on the General Assembly, Jason Seifer compares the number of Ruby virtual machine you realize the difference between, and pointed out that enterprises should be how to choose the suitable Ruby virt

  • ruby regular expression match but does not contain

    There is a demand to resolve (sdbeis) To the string in brackets Can also be <html> ...</ html> The ordinary course of events should be used Or Or This in fact, match but not in the scope of the regular expression, useful only in the latte ...

  • JavaScript Array Object Reference Manual (directory)

    JavaScript Array Object Reference Manual Note: FF = Firefox, N = Netscape, IE = Internet Explorer Array Object Ways Description FF N IE concat () Connect two or more of the array, and return results 1 4 4 join () Put all the elements of the array int ...

  • Learning ruby on rails video tutorials and e-book download

    25, and are not large, fast download! Books, personal recommend Agile Web Development with Rails (Chinese version translated well, e-books is the first version 2) and Rails Recipes (print out bar, Value!), And ruby for rail ...

  • rails to add full-featured auto-fill

    Automatic text box is a complete help users quickly and accurately fill out the form function, which is based on the database already exists in certain fields of data. We have Google Suggest ( as an example of such a fea

  • js commonly used in integrated

    / / Open the model dialog box function doSelectUser (txtId) ( strFeatures = "dialogWidth = 500px; dialogHeight = 360px; center = yes; middle = yes; help = no; status = no; scroll = no"; var url, strReturn; url = "selUser.aspx"; strRetu

  • javascript: history.go () and History.back () the difference between collections

    <input type=button value= Refresh > <input type=button value= Forward > <input type=button value= Back > <input type=button value= Forward > <input type=button value= Back > Back + refresh <input type=button value= Ba ...

  • javascript regular expressions Basic Usage

    <script type="text/javascript"> window.onload = function(){ //RegExpInitialization ,gRefers to the global ,iIs case-insensitive var reg = new RegExp("cat","gi"); var exp = /cat/gi; //RegExpThe test ()Method ,The return value is true or false alert(r

  • Detailed JS regular expressions

    Opening, or have to talk about ^ and $ that they are separately used to match the beginning and end of the string, the following examples to illustrate separately "^ The": there must be at the beginning of "The" string; "of despai

  • Based on Spring's Hibernate Search full-text search function of sample

    Database: Oracle 9i JDBC Driver: OJDBC14 Development Environment: Eclipse-JEE Spring version: Spring 2.0.6 Hibernate version: Hibernate Core 3.2.5/Hibernate Annotation 3.3.0/Hibernate Validator 3.0.0/Hibernate Search 3.0.0 Beta4 / / (JDBC

blog comments powered by Disqus
Recent Entries
Tag Cloud
Random Entries