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.
