MySQL introduced full-text indexing and searching capabilities back in version 3.23.23. The implementation is straightforward and easy to use—define a FULLTEXT index and use MATCH/AGAINST in the query. Consider this example:
CREATE TABLE WROX_SOCIAL_EVENT ( EVENT_ID INTEGER UNSIGNED NOT NULL AUTO_INCREMENT, USER_ID INTEGER UNSIGNED NOT NULL, HEADLINE TEXT NOT NULL, EVENT_TEXT TEXT NOT NULL, EVENT_DATE TIMESTAMP NOT NULL, PRIMARY KEY (EVENT_ID), FOREIGN KEY (USER_ID) REFERENCES WROX_SOCIAL_USER(USER_ID), FULLTEXT INDEX (HEADLINE, EVENT_TEXT) ) ENGINE=MyISAM DEFAULT CHARACTER SET latin1 COLLATE latin1_general_cs AUTO_INCREMENT=0;
Assume this table is for a social networking web site where users post upcoming events and visitors want searches against information in the HEADLINE and EVENT_TEXT columns to find events of interest to them. A full-text search query might look like this:
SELECT EVENT_ID, U.USER_ID, U.USERNAME, HEADLINE, EVENT_TEXT, EVENT_DATE FROM WROX_SOCIAL_EVENT E JOIN WROX_SOCIAL_USER U ON E.USER_ID = U.USER_ID WHERE MATCH (HEADLINE, EVENT_TEXT) AGAINST ('<search term>'),
Unfortunately, there are some drawbacks to depending on MySQL's full-text search functionality. Full-text indexes can only be used with the MyISAM engine, which doesn't strictly enforce foreign key constraints or perform row-level locking like InnoDB does. Many times MyISAM is a good choice, but if your application depends on InnoDB features, then you won't be able to perform full-text searching.
Another problem is that the list of stop words MySQL uses is directly compiled in to the binary. Suppose a new event is added for a one-day workshop titled "Help Me Look Sensible" designed to help recent college graduates learn how to dress professionally before they go out into the workplace. Each word in the title appears on MySQL's stop list, so even searching for the event by name would return zero results! To modify the list, you must edit the myisam/ft_static.c source file and then recompile MySQL. This may not be a viable option for you depending on your hosting situation.
As a third point, MySQL only indexes words that are four characters or greater in length. This might pose a problem if the same college graduate wants to search for PHP (thankfully, beer is four letters long).
There are also some logistical/performance implications for using full-text searching. The entire document must be stored in the database which leads to needless wasted space. (An inverted index doesn't store stop words and condenses duplicate words to one-to-many relationships with integer keys.) This wasted space isn't necessarily an issue with a small number of documents, but may become one as the number of pages increases. Search performance can decrease exponentially, making the user wait needless seconds or minutes for the results to be returned.
18.119.213.111