Database Reference
In-Depth Information
| tin | 1 | 1 |
+----------+------------------+------------------+
LOCATE() , LIKE , and REGEXP use the collation of their arguments to determine whether
the search is case sensitive. Recipes 5.5 and 5.7 discuss changing the argument com‐
parison properties if you want to change the search behavior.
5.12. Using Full-Text Searches
Problem
You want to search a lot of text.
Solution
Use a FULLTEXT index.
Discussion
Pattern matches enable you to look through any number of rows, but as the amount of
text goes up, the match operation can become quite slow. It's also a common task to
search for the same text in several string columns, but with pattern matching, that results
in unwieldy queries:
SELECT * from tbl_name
WHERE col1 LIKE ' pat ' OR col2 LIKE ' pat ' OR col3 LIKE ' pat ' ...
A useful alternative is full-text searching, which is designed for looking through large
amounts of text and can search multiple columns simultaneously. To use this capability,
add a FULLTEXT index to your table, and then use the MATCH operator to look for strings
in the indexed column or columns. FULLTEXT indexing can be used with MyISAM tables
(or, as of MySQL 5.6, InnoDB tables) for nonbinary string data types ( CHAR , VARCHAR ,
or TEXT ).
Full-text searching is best illustrated with a reasonably good-sized body of text. If you
don't have a sample dataset, you can find several repositories of freely available electronic
text on the Internet. For the examples here, the one I've chosen is the complete text of
the King James Version of the Bible (KJV), which is both relatively large and nicely
structured by book, chapter, and verse. Because of its size, this dataset is not included
with the recipes distribution, but is available separately as the mcb-kjv distribution at
the MySQL Cookbook website (see the Preface ). The mcb-kjv distribution includes a file
named kjv.txt that contains the verse records. Some sample records look like this:
O Genesis 1 1 1 In the beginning God created the heaven and the earth.
O Exodus 2 20 13 Thou shalt not kill.
N Luke 42 17 32 Remember Lot's wife.
Search WWH ::




Custom Search