Searching with Strings - Narrowing the Search
(Page 4 of 4 )
You can include additional criteria to narrow the search further. The following queries perform progressively more specific searches to find out how often the name Abraham occurs in the entire KJV, the New Testament, the Book of Hebrews, and Chapter 11 of Hebrews:
mysql> SELECT COUNT(*) from kjv WHERE MATCH(vtext) AGAINST('Abraham');
+----------+
| COUNT(*) |
+----------+
| 216 |
+----------+
mysql> SELECT COUNT(*) from kjv
-> WHERE MATCH(vtext) AGAINST('Abraham')
-> AND bsect = 'N';
+----------+
| COUNT(*) |
+----------+
| 66 |
+----------+
mysql> SELECT COUNT(*) from kjv
-> WHERE MATCH(vtext) AGAINST('Abraham')
-> AND bname = 'Hebrews';
+----------+
| COUNT(*) |
+----------+
| 10 |
+----------+
mysql> SELECT COUNT(*) from kjv
-> WHERE MATCH(vtext) AGAINST('Abraham')
-> AND bname = 'Hebrews' AND cnum = 11;
+----------+
| COUNT(*) |
+----------+
| 2 |
+----------+
If you expect to use search criteria that include other non-FULLTEXT columns frequently, you can increase the performance of such queries by adding regular indexes to those columns. For example, to index the book, chapter, and verse number columns, do this:
mysql> ALTER TABLE kjv ADD INDEX (bnum), ADD INDEX (cnum), ADD INDEX (vnum);
Search strings in FULLTEXT queries can include more than just a single word, and you might suppose that adding additional words would make a search more specific. But in fact that widens it, because a FULLTEXT search returns rows that contain any of the words. In effect, the query performs an OR search for any of the words. This is illustrated by the following queries, which identify successively larger numbers of verses as additional search words are added:
mysql> SELECT COUNT(*) from kjv
-> WHERE MATCH(vtext) AGAINST('Abraham');
+----------+
| COUNT(*) |
+----------+
| 216 |
+----------+
mysql> SELECT COUNT(*) from kjv
-> WHERE MATCH(vtext) AGAINST('Abraham Sarah');
+----------+
| COUNT(*) |
+----------+
| 230 |
+----------+
mysql> SELECT COUNT(*) from kjv
-> WHERE MATCH(vtext) AGAINST('Abraham Sarah Ishmael Isaac');
+----------+
| COUNT(*) |
+----------+
| 317 |
+----------+
To perform a search in which each word in the search string must be present, see Recipe 5.17 .
If you want to use a FULLTEXT search that looks through multiple columns simultaneously, name them all when you construct the index:
ALTER TABLE tbl_name ADD FULLTEXT (col1, col2, col3);
To issue a search query that uses this index, name those same columns in the MATCH() list:
SELECT ... FROM tbl_name
WHERE MATCH(col1, col2, col3) AGAINST('search string');
You’ll 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 web site (see Appendix A) 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.
Please check back next week for the conclusion to this article.
| DISCLAIMER: The content provided in this article is not warranted or guaranteed by Developer Shed, Inc. The content provided is intended for entertainment and/or educational purposes in order to introduce to the reader key ideas, concepts, and/or product reviews. As such it is incumbent upon the reader to employ real-world tactics for security and implementation of best practices. We are not liable for any negative consequences that may result from implementing any information covered in our articles or tutorials. If this is a hardware review, it is not recommended to open and/or modify your hardware. |
|
 This article is excerpted from chapter five of the MySQL Cookbook, Second Edition, written by Paul DuBois (O'Reilly; ISBN: 059652708X). Check it out today at your favorite bookstore. Buy this book now.
|
|
