MATCH AGAINST or LIKE: Which is Best for Keyword Searches in MySQL?

MATCH AGAINST vs. LIKE: Performance and Keyword Detection Considerations

In a situation where you need to search a database for rows containing keywords "foo" and "bar" within specific columns, you have the option of using either MATCH AGAINST or LIKE. But which one is the better choice?

MATCH AGAINST boasts significant performance advantages when used with MyISAM tables, as it leverages full-text indexes for faster search. In contrast, LIKE performs full table scans, which become increasingly inefficient as the number of rows in the table grows.

However, LIKE offers an advantage in keyword detection, as it can detect partial matches such as 'xxfoo', while MATCH AGAINST requires full word matches. If this functionality is crucial, you may need to consider LIKE, even at the cost of performance.

In the case of MySQL 5.6 and later, InnoDB tables also support MATCH AGAINST, providing a more comprehensive solution that addresses both performance and keyword matching capabilities.

Therefore, the preferred choice depends on your specific requirements and database environment. For optimal performance on MyISAM tables or InnoDB tables in MySQL 5.6 and later, MATCH AGAINST is generally the better option. However, if you require partial keyword detection, LIKE may still be a viable choice, albeit with potential performance implications for larger datasets.

The above is the detailed content of MATCH AGAINST or LIKE: Which is Best for Keyword Searches in MySQL?. For more information, please follow other related articles on the PHP Chinese website!