Searching with Strings - Using FULLTEXT Searches continued
(Page 3 of 4 )
It’s possible to include the index definition in the initial CREATE TABLE statement, but it’s generally faster to create a nonindexed table and then add the index with ALTER TABLE after populating the table than to load a large dataset into an indexed table.
To perform a search using the index, use MATCH() to name the indexed column and AGAINST() to specify what text to look for. For example, you might wonder, “How many times does the name Mizraim occur?” To answer that question, search the vtext column using this statement:
mysql> SELECT COUNT(*) from kjv WHERE MATCH(vtext) AGAINST('Mizraim');
+----------+
| COUNT(*) |
+----------+
| 4 |
+----------+
To find out what those verses are, select the columns you want to see (the example here uses \G so that the results better fit the page):
mysql> SELECT bname, cnum, vnum, vtext
-> FROM kjv WHERE MATCH(vtext) AGAINST('Mizraim')\G
*************************** 1. row ***************************
bname: Genesis
cnum: 10
vnum: 6
vtext: And the sons of Ham; Cush, and Mizraim, and Phut, and Canaan.
*************************** 2. row ***************************
bname: Genesis
cnum: 10
vnum: 13
vtext: And Mizraim begat Ludim, and Anamim, and Lehabim, and Naphtuhim,
*************************** 3. row ***************************
bname: 1 Chronicles
cnum: 1
vnum: 8
vtext: The sons of Ham; Cush, and Mizraim, Put, and Canaan.
*************************** 4. row ***************************
bname: 1 Chronicles
cnum: 1
vnum: 11
vtext: And Mizraim begat Ludim, and Anamim, and Lehabim, and Naphtuhim,
The results come out in book, chapter, and verse number order in this particular instance, but that’s actually just coincidence. By default, FULLTEXT searches compute a relevance ranking and use it for sorting. To make sure a search result is sorted the way you want, add an explicit ORDER BY clause:
SELECT bname, cnum, vnum, vtext
FROM kjv WHERE MATCH(vtext) AGAINST('search string')
ORDER BY bnum, cnum, vnum;
If you want to see the relevance ranking, repeat the MATCH() ... AGAINST() expression in the output column list.
Next: Narrowing the Search >>
More Database Articles Articles
More By O'Reilly Media
|
 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.
|
|
