Oracle 5.0, Reference Manual

Page: 956 / 3232

Natural Language Full-Text Searches
936
By default, the search is performed in case-insensitive fashion. However, you can perform a case-
sensitive full-text search by using a binary collation for the indexed columns. For example, a column
that uses the
latin1
character set of can be assigned a collation of
latin1_bin
to make it case
sensitive for full-text searches.
When
MATCH()
[934] is used in a
WHERE
clause, as in the example shown earlier, the rows returned
are automatically sorted with the highest relevance first. Relevance values are nonnegative 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.
To simply count matches, you could use a query like this:
mysql> SELECT COUNT(*) FROM articles
-> WHERE MATCH (title,body)
-> AGAINST ('database');
+----------+
| COUNT(*) |
+----------+
| 2 |
+----------+
1 row in set (0.00 sec)
However, you might find it quicker to rewrite the query as follows:
mysql> SELECT
-> COUNT(IF(MATCH (title,body) AGAINST ('database'), 1, NULL))
-> AS count
-> FROM articles;
+-------+
| count |
+-------+
| 2 |
+-------+
1 row in set (0.00 sec)
The first query sorts the results by relevance whereas the second does not. However, the second
query performs a full table scan and the first does not. The first may be faster if the search matches few
rows; otherwise, the second may be faster because it would read many rows anyway.
For natural-language full-text searches, it is a requirement that the columns named in the
MATCH()
[934] function be the same columns included in some
FULLTEXT
index in your table. For
the preceding query, note that the columns named in the
MATCH()
[934] function (
title
and
body
)
are the same as those named in the definition of the
article
table's
FULLTEXT
index. If you wanted
to search the
title
or
body
separately, you would need to create separate
FULLTEXT
indexes for
each column.
It is also possible to perform a boolean search or a search with query expansion. These search types
are described in
Section 12.9.2, “Boolean Full-Text Searches” , and Section 12.9.3, “Full-Text Searches
with Query Expansion” .
A full-text search that uses an index can name columns only from a single table in the
MATCH()
[934]
clause because an index cannot span multiple tables. A boolean search can be done in the absence of
an index (albeit more slowly), in which case it is possible to name columns from multiple tables.
The preceding example is a basic illustration that shows how to use the
MATCH()
[934] function
where rows are returned in order of decreasing relevance. The next example shows how to retrieve the
relevance values explicitly. Returned rows are not ordered because the
SELECT
statement includes
neither
WHERE
nor
ORDER BY
clauses:
mysql> SELECT id, MATCH (title,body) AGAINST ('Tutorial')
-> FROM articles;
+----+-----------------------------------------+
| id | MATCH (title,body) AGAINST ('Tutorial') |
+----+-----------------------------------------+
| 1 | 0.65545833110809 |