Database Reference
In-Depth Information
+----------+
| 334 |
+----------+
To perform a search for which each word in the search string must be present, see
Recipe 5.14
.
To use full-text searches that look through multiple columns simultaneously, name all
the columns when you construct the
FULLTEXT
index:
ALTER
TABLE
tbl_name
ADD
FULLTEXT
(
col1
,
col2
,
col3
);
To issue a search query that uses the index, name those same columns in the
MATCH()
list:
SELECT
...
FROM
tbl_name
WHERE
MATCH
(
col1
,
col2
,
col3
)
AGAINST
(
'
search
string
'
);
You need one such
FULLTEXT
index for each distinct combination of columns that you
want to search.
See Also
FULLTEXT
indexes provide a quick-and-easy way to set up a basic search engine. One
way to use this capability is to provide a web-based interface to the indexed text. This
book's website (see the
Preface
) includes a simple web-based KJV search page that
demonstrates this. You can use it as the basis for your own search engine that operates
on a different repository of text. The search script,
kjv.pl
, is included in the
mcb-kjv
distribution.
5.13. Using a Full-Text Search with Short Words
Problem
Full-text searches for short words return no rows.
Solution
Change the indexing engine's minimum word length parameter.
Discussion
In a text like the KJV, certain words have special significance, such as “God” and “sin.”
However, if your
kjv
table uses the MyISAM storage engine and you perform full-text
searches for those words, you'll observe a curious phenomenon—both words appear to
be missing from the text entirely:
