Full-Text Searching

Often, a database query contains a search where you're not exactly sure what you're looking for but you know it might be like this or that. You may have already seen this while using LIKE in a WHERE conditional. An alternative is the full-text search, which has two benefits over LIKE:

• You can search using multiple words.

• Full-text searches are much, much faster.

There are four gotchas when it comes to full-text searches:

• Full-text searches are case-insensitive.

• If you search using a term (or terms) that appears in more than half of the records, no rows will be returned, because that search will not be specific enough. One workaround is to use Boolean mode, discussed later in this chapter.

• There is a minimum word length for search terms, by default four characters. Shorter terms will never be found, even when you know they are present.

• Very popular words, called stopwords, are ignored.

Full-text searching is most important in situations where people will be entering keywords that must be searched against specific fields in a table, such as search engines. I'll first show you how to create the proper FULLTEXT index. Then I'll run through some simple full-text searches. Finally, you'll learn about the full-text Boolean mode, which allows for much more precise searching.

Creating a FULLTEXT index

Before performing a full-text search, you need to prepare the database by creating an index of type FULLTEXT on the column or columns to be used in the searches. Currently, you can only create FULLTEXT searches on MyISAM tables.

To create a FULLTEXT index when defining a table, you would do something like this:

CREATE TABLE tablename (
col1 TEXT,
col2 TEXT,
FULLTEXT (col1, col2)
)

If the table already exists, you can add a FULLTEXT index using an ALTER query:

ALTER TABLE tablename ADD FULLTEXT (columns)

I'll modify the expenses table (in the accounting database) to add a FULLTEXT index on the expense_description column.

To create a FULLTEXT index:

1.	Log in to the mysql client as a user that can access and modify the accounting database.
2.	Select the accounting database. USE accounting;
3.	Make sure the expenses table is well populated (Figure 10.9). Figure 10.9. You'll need to have quite a bit of data in your table in order to best use full-text searches. [View full size image] SELECT expense_description FROM expenses; It doesn't really matter what SQL you use during these steps as long as you add records with good descriptions to the expenses table. The more records a table has, the more useful and accurate full-text indexing and searching becomes.
4.	Create a full-text index (Figure 10.10). Figure 10.10. A FULLTEXT index is added to the expenses table. ALTER TABLE expenses ADD FULLTEXT (expense_description); Before I run any full-text searches on a table, I must create a full-text index on the column or columns involved.
5.	Confirm the table's structure by viewing its CREATE syntax (Figure 10.11). Figure 10.11. The SHOW CREATE TABLE query indicates what command would be used to make the table. [View full size image] SHOW CREATE TABLE expenses \G You've already seen a couple of SHOW commands by now, so hopefully this one isn't too surprising. The SHOW CREATE TABLE tablename command asks MySQL to print out the SQL command used to create (or re-create) the named table. Using the \G trick makes the results easier to view.

Performing basic FULLTEXT searches

Once you've established a FULLTEXT index on a column, you can start querying against it, using the MATCH and AGAINST keywords. The syntax for using full-text searching is

SELECT * FROM tablename WHERE MATCH (column) AGAINST ('string')

The result of the query will be the rows returned in order of relevance from most to least. In other words, the rows in which string most matches the values in column will be listed first.

If you want to match against multiple words, you can do so, separating the words by spaces:

SELECT * FROM tablename WHERE MATCH
   (column) AGAINST ('word1 word2')

With a query like this, rows that contain both words will rank higher than those that contain only one or the other (which would still qualify as a match). This behavior can be tweaked using the Boolean mode, discussed next in the chapter.

To view the relevance of a row returned by a match, you would select it:

SELECT somecolumn, MATCH (column) AGAINST ('string') FROM tablename

FULLTEXT Indexes, Revisited Your FULLTEXT index must be created on the same column or combination of columns that you use in your SELECT queries. If you create the index on one column, you can use only that one column in your query. If you create the index on two columns, you must use exactly both those columns in your queries: ALTER TABLE tablename ADD FULLTEXT (col1, col2) SELECT * FROM tablename WHERE MATCH (col1, col2) AGAINST ('string')

To use full-text searching:

1.	Log in to the mysql client and select the accounting database, if you have not already. USE accounting;
2.	Make sure the expenses table is well populated. If you are paying close attention, you'll note that this step was included in the preceding sequence. It's important, though. The more populated a table is, the more useful a full-text search is. In fact, a (non-Boolean mode) full-text search won't even work if you have fewer than three records in the table!
3.	Do a full-text search using the word visual (Figure 10.12). Figure 10.12. A simple full-text search quickly returns all records that contain the word visual within the expense description. [View full size image] SELECT expense_id, expense_description FROM expenses WHERE MATCH (expense_description) AGAINST ('visual'); This query will return the expense_id and expense_description columns wherever visual has a positive relevance in the expense_description values. In other words, any record that contains the word visual will be returned. If you have different data in your table, you'll want to change the searched-for word accordingly.
4.	Run a full-text search using the words visual and guide (Figure 10.13). Figure 10.13. This search returns every record that contains either visual or guide but ranks records with both higher. [View full size image] SELECT expense_id, expense_description, MATCH (expense_description) AGAINST ('visual guide') AS rel FROM expenses WHERE MATCH (expense_description) AGAINST ('visual guide') \G This query differs from that in Step 3 in two ways. First, I'm also selecting the MATCH...AGAINST value so that the calculated relevance number is displayed. Second, I've included a second term to match. Those records with both terms will score higher than those with just one.

Performing Boolean FULLTEXT searches

You can take your full-text searching one step further by using its Boolean mode (as of MySQL version 4.0.1). In Boolean mode the search terms can be preceded by special characters (Table 10.1) to indicate how their presence should be weighted with regard to relevancy.

Table 10.1. These characters are specifically used to affect the importance of terms in full-text searches.

Special Boolean Mode Characters
Character	Meaning	Example	Matches
+	Word is required	+punk rock	punk is required and rock is optional
-	Word must not be present	+punk -rock	punk is required and rock cannot be present
""	A literal phrase	"punk rock"	Occurrences of the phrase punk rock are weighted
<	Less important	<punk +rock	rock is required, and punk is less significant
>	More important	>punk +rock	rock is required, but punk is more significant
()	Creates groups	(>punk roll) +rock	rock is required, both punk and roll are optional, but punk is weighted more
~	Detracts from relevance	+punk ~rock	punk is required, and the presence of rock devalues the relevance (but rock is not excluded)
*	Allows for wildcards	+punk +rock*	punk and rock are required, but rocks, rocker, rocking, etc., are counted

SELECT columns FROM tablename WHERE MATCH (column) AGAINST ('+word1 word2' IN BOOLEAN MODE)
SELECT * FROM tablename WHERE MATCH (column) AGAINST ('+database -mysql' IN BOOLEAN MODE)

In the second example, a match will be made if the word database is found and mysql is not present. Alternatively, the tilde (~) is used as a milder form of the minus sign, meaning that the keyword can be present in a match, but such matches should be considered less relevant.

The wildcard character (*) matches variations on a word, so cata* matches catalog, catalina, and so on. Two operators explicitly state what keywords are more (>) or less (<) important. Finally, you can use double quotation marks to hunt for exact phrases. Parentheses can be used to make subexpressions.

The following query would look for records with the phrase Web develop with the word html being required and the word JavaScript detracting from a match's relevance:

SELECT * FROM tablename WHERE
MATCH(column) AGAINST('>"Web develop" +html ~JavaScript' IN BOOLEAN MODE)

When using Boolean mode, there are several differences as to how FULLTEXT searches work:

• If a keyword is not preceded by an operator, the word is optional but a match will be ranked higher if it is present.

• Results will be returned even if more than 50 percent of the records match the search.

• The results are not automatically sorted by relevance.

Because of this last fact, you'll also want to sort the returned records by their relevance, as I'll demonstrate in the next sequence of steps. One important rule that remains with Boolean searches is that the minimum word length (four characters by default) still applies. So trying to require a shorter word using a plus sign (+SQL) still won't work.

Control Flow Functions One advanced topic that I don't cover in this book is the various control flow functions MySQL has to offer. The basic function is an IF conditional, which acts like a ternary operator: IF (condition, return_this_if_true, return_this_if_false) If the condition is true, the second argument is returned. Otherwise, the third argument is returned. This next example returns either odd or even, depending upon whether or not the remainder of dividing a column's value (e.g., 3) by 2 is equal to 1: SELECT IF( (some_num_col % 2) = 1, 'odd', 'even'); Along with the IF conditional, there are an IFNULL, a NULLIF, and a CASE. Each is really just a specific type of conditional. See the MySQL manual for syntax and examples of these.

To use Boolean mode:

1.	Log in to the mysql client and select the accounting database, if you have not already. USE accounting;
2.	Do a full-text search requiring both the words visual and guide (Figure 10.14). Figure 10.14. Both keywords are required, resulting in one less match (compare with Figure 10.13). [View full size image] SELECT expense_id, expense_description, MATCH (expense_description) AGAINST ('+visual +guide' IN BOOLEAN MODE) AS rel FROM expenses WHERE MATCH (expense_description) AGAINST ('+visual +guide' IN BOOLEAN MODE) ORDER BY rel DESC \G This query adds two new features. First, the IN BOOLEAN MODE text is added, as well as the plus signs, indicating required words. Second, the results are ordered by their relevance, using an alias.
3.	Do a full-text search requiring both the words visual and guide while stressing the word quickpro (Figure 10.15). Figure 10.15. The relevance of the results was altered by placing extra importance on quickpro (compare with Figure 10.14). [View full size image] [View full width] SELECT expense_id, expense_description, MATCH (expense_description) AGAINST ('+visual +guide >quickpro' IN BOOLEAN MODE) AS rel FROM expenses WHERE MATCH (expense_description) AGAINST ('+visual +guide >quickpro' IN BOOLEAN MODE) ORDER BY rel DESC \G I add >quickpro to my search terms to give preference to records containing this word.