4.2. Problems with Full-Text Search
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 ...
Get PHP and MySQL®: Create-Modify-Reuse now with the O’Reilly learning platform.
O’Reilly members experience books, live events, courses curated by job role, and more from O’Reilly and nearly 200 top publishers.