 |
 |
|
|
| [ < ] | [ > ] | [ << ] | [ Up ] | [ >> ] | [Top] | [Contents] | [Index] | [ ? ] |
As of Version 3.23.23, MySQL has support for full-text indexing
and searching. Full-text indexes in MySQL are an index of type
FULLTEXT. FULLTEXT indexes are used with MyISAM tables
only and can be created from CHAR, VARCHAR,
or TEXT columns at CREATE TABLE time or added later with
ALTER TABLE or CREATE INDEX. For large datasets, it will be
much faster to load your data into a table that has no FULLTEXT
index, then create the index with ALTER TABLE (or CREATE
INDEX). Loading data into a table that already has a FULLTEXT
index will be slower.
Full-text searching is performed with the MATCH() function.
mysql> CREATE TABLE articles (
-> id INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY,
-> title VARCHAR(200),
-> body TEXT,
-> FULLTEXT (title,body)
-> );
Query OK, 0 rows affected (0.00 sec)
mysql> INSERT INTO articles VALUES
-> (NULL,'MySQL Tutorial', 'DBMS stands for DataBase ...'),
-> (NULL,'How To Use MySQL Efficiently', 'After you went through a ...'),
-> (NULL,'Optimising MySQL','In this tutorial we will show ...'),
-> (NULL,'1001 MySQL Tricks','1. Never run mysqld as root. 2. ...'),
-> (NULL,'MySQL vs. YourSQL', 'In the following database comparison ...'),
-> (NULL,'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');
+----+-------------------+------------------------------------------+
| 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)
|
The MATCH() function performs a natural language search for a string
against a text collection (a set of one or more columns included in
a FULLTEXT index). The search string is given as the argument to
AGAINST(). The search is performed in case-insensitive fashion.
For every row in the table, MATCH() returns a relevance value,
that is, a similarity measure between the search string and the text in
that row in the columns named in the MATCH() list.
When MATCH() is used in a WHERE clause (see example above)
the rows returned are automatically sorted with highest relevance first.
Relevance values are non-negative floating-point numbers. Zero relevance
means no similarity. Relevance is computed based on the number of words
in the row, the number of unique words in that row, the total number of
words in the collection, and the number of documents (rows) that contain
a particular word.
It is also possible to perform a boolean mode search. This is explained later in the section.
The preceding example is a basic illustration showing how to use the
MATCH() function. Rows are returned in order of decreasing
relevance.
The next example shows how to retrieve the relevance values explicitly.
As neither WHERE nor ORDER BY clauses are present, returned
rows are not ordered.
mysql> SELECT id,MATCH (title,body) AGAINST ('Tutorial') FROM articles;
+----+-----------------------------------------+
| id | MATCH (title,body) AGAINST ('Tutorial') |
+----+-----------------------------------------+
| 1 | 0.64840710366884 |
| 2 | 0 |
| 3 | 0.66266459031789 |
| 4 | 0 |
| 5 | 0 |
| 6 | 0 |
+----+-----------------------------------------+
6 rows in set (0.00 sec)
|
The following example is more complex. The query returns the relevance
and still sorts the rows in order of decreasing relevance. To achieve
this result, you should specify MATCH() twice. This will cause no
additional overhead, because the MySQL optimiser will notice that the
two MATCH() calls are identical and invoke the full-text search
code only once.
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');
+----+-------------------------------------+-----------------+
| id | body | score |
+----+-------------------------------------+-----------------+
| 4 | 1. Never run mysqld as root. 2. ... | 1.5055546709332 |
| 6 | When configured properly, MySQL ... | 1.31140957288 |
+----+-------------------------------------+-----------------+
2 rows in set (0.00 sec)
|
MySQL uses a very simple parser to split text into words. A "word" is any sequence of characters consisting of letters, digits, `'', and `_'. Any "word" that is present in the stopword list or is just too short is ignored. The default minimum length of words that will be found by full-text searches is four characters. This can be changed as described in Fine-tuning MySQL Full-text Search.
Every correct word in the collection and in the query is weighted according to its significance in the query or collection. This way, a word that is present in many documents will have lower weight (and may even have a zero weight), because it has lower semantic value in this particular collection. Otherwise, if the word is rare, it will receive a higher weight. The weights of the words are then combined to compute the relevance of the row.
Such a technique works best with large collections (in fact, it was carefully tuned this way). For very small tables, word distribution does not reflect adequately their semantic value, and this model may sometimes produce bizarre results.
mysql> SELECT * FROM articles WHERE MATCH (title,body) AGAINST ('MySQL');
Empty set (0.00 sec)
|
The search for the word MySQL produces no results in the above
example, because that word is present in more than half the rows. As such,
it is effectively treated as a stopword (that is, a word with zero semantic
value). This is the most desirable behaviour - a natural language query
should not return every second row from a 1 GB table.
A word that matches half of rows in a table is less likely to locate relevant documents. In fact, it will most likely find plenty of irrelevant documents. We all know this happens far too often when we are trying to find something on the Internet with a search engine. It is with this reasoning that such rows have been assigned a low semantic value in this particular dataset.
As of Version 4.0.1, MySQL can also perform boolean full-text searches using
the IN BOOLEAN MODE modifier.
mysql> SELECT * FROM articles WHERE MATCH (title,body)
-> AGAINST ('+MySQL -YourSQL' IN BOOLEAN MODE);
+----+------------------------------+-------------------------------------+
| id | title | body |
+----+------------------------------+-------------------------------------+
| 1 | MySQL Tutorial | DBMS stands for DataBase ... |
| 2 | How To Use MySQL Efficiently | After you went through a ... |
| 3 | Optimising MySQL | In this tutorial we will show ... |
| 4 | 1001 MySQL Tricks | 1. Never run mysqld as root. 2. ... |
| 6 | MySQL Security | When configured properly, MySQL ... |
+----+------------------------------+-------------------------------------+
|
This query retrieved all the rows that contain the word MySQL
(note: the 50% threshold is not used), but that do not contain
the word YourSQL. Note that a boolean mode search does not
automatically sort rows in order of decreasing relevance. You can
see this from result of the preceding query, where the row with the
highest relevance (the one that contains MySQL twice) is listed
last, not first. A boolean full-text search can also work even without
a FULLTEXT index, although it would be slow.
The boolean full-text search capability supports the following operators:
+A leading plus sign indicates that this word must be present in every row returned.
-A leading minus sign indicates that this word must not be present in any row returned.
By default (when neither plus nor minus is specified) the word is optional,
but the rows that contain it will be rated higher. This mimicks the
behaviour of MATCH() ... AGAINST() without the IN BOOLEAN
MODE modifier.
< >These two operators are used to change a word's contribution to the
relevance value that is assigned to a row. The < operator
decreases the contribution and the > operator increases it.
See the example below.
( )Parentheses are used to group words into subexpressions.
~A leading tilde acts as a negation operator, causing the word's
contribution to the row relevance to be negative. It's useful for marking
noise words. A row that contains such a word will be rated lower than
others, but will not be excluded altogether, as it would be with the
- operator.
*An asterisk is the truncation operator. Unlike the other operators, it should be appended to the word, not prepended.
"The phrase, that is enclosed in double quotes ", matches only
rows that contain this phrase literally, as it was typed.
And here are some examples:
apple bananafind rows that contain at least one of these words.
+apple +juice... both words.
+apple macintosh... word "apple", but rank it higher if it also contain "macintosh".
+apple -macintosh... word "apple" but not "macintosh".
+apple +(>turnover <strudel)... "apple" and "turnover", or "apple" and "strudel" (in any order), but rank "apple pie" higher than "apple strudel".
apple*... "apple", "apples", "applesauce", and "applet".
"some words"... "some words of wisdom", but not "some noise words".
| 6.8.1 Full-text Restrictions | ||
| 6.8.2 Fine-tuning MySQL Full-text Search | ||
| 6.8.3 Full-text Search TODO |
MyISAM tables only.
MATCH() function must be columns from the
same table that is part of the same FULLTEXT index, unless the
MATCH() is IN BOOLEAN MODE.
MATCH() column list must exactly match the column list in some
FULLTEXT index definition for the table, unless this MATCH()
is IN BOOLEAN MODE.
AGAINST() must be a constant string.
Unfortunately, full-text search has few user-tunable parameters yet, although adding some is very high on the TODO. If you have a MySQL source distribution (see section Installing a MySQL Source Distribution), you can exert more control over full-text searching behaviour.
Note that full-text search was carefully tuned for the best searching effectiveness. Modifying the default behaviour will, in most cases, only make the search results worse. Do not alter the MySQL sources unless you know what you are doing!
The full-text variables described in the following list must be set at server startup time. You cannot modify them dynamically while the server is running.
ft_min_word_len.
See section ft_min_word_len.
(This variable is only available from MySQL version 4.0.)
The default value is four characters.
Change it to the value you prefer, and rebuild your FULLTEXT indexes.
For example, if you want three-character words to be searchable, you can set
this variable by putting the following lines in an option file:
[mysqld] ft_min_word_len=3 |
Then restart the server and rebuild your FULLTEXT indexes.
ft_stopword_file variable.
See section ft_stopword_file.
Rebuild your FULLTEXT indexes after modifying the stopword list.
(This variable is only available from MySQL version 4.0.10 and onwards)
#define GWS_IN_USE GWS_PROB |
To:
#define GWS_IN_USE GWS_FREQ |
Then recompile MySQL.
There is no need to rebuild the indexes in this case.
Note: by doing this you severely decrease MySQL's ability
to provide adequate relevance values for the MATCH() function.
If you really need to search for such common words, it would be better to
search using IN BOOLEAN MODE instead, which does not observe the 50%
threshold.
ft_boolean_syntax variable.
See section ft_boolean_syntax.
Still, this variable is read-only; its value is set in
`myisam/ft_static.c'.
For full-text changes that require you to rebuild your FULLTEXT indexes,
the easiest way to do so for a MyISAM table is to use the following
statement, which rebuilds the index file:
mysql> REPAIR TABLE tbl_name QUICK; |
FULLTEXT index faster.
MERGE tables.
FULLTEXT in CREATE/ALTER TABLE).
| [ < ] | [ > ] | [ << ] | [ Up ] | [ >> ] |
Hosting by: Hurra Communications Ltd.
Generated: 2007-01-26 17:58:45