MATCH AGAINST or LIKE: Which is Better for Multi-Column Keyword Searches in MySQL?

Choosing Between MATCH AGAINST and LIKE for Database Search

In database queries, one faces the dilemma of selecting the optimal query for searching rows based on specific keywords in multiple columns. This question compares two approaches:

MATCH AGAINST

This query uses full-text indexing to search for documents containing the keywords "foo" and "bar" in the specified columns "foo_desc" and "bar_desc." In MySQL versions 5.6 and later, MATCH AGAINST is supported for InnoDB tables.

LIKE

This query concatenates the values from the two columns and employs LIKE to perform a substring search for "foo" and "bar" within the resulting string.

Performance Considerations

MATCH AGAINST exhibits superior performance on MyISAM tables, leveraging full-text indexes to efficiently search for the keywords. In contrast, LIKE performs a full table scan, searching each row for the keywords, which can be time-consuming for large datasets.

Limitations of LIKE

LIKE is effective only if applied to a column directly (not a function result), if the search pattern matches the beginning of the column, and if the column is indexed. Otherwise, it defaults to a full table scan, becoming impractical for large datasets.

Drawbacks of MATCH AGAINST

One limitation of MATCH AGAINST in MySQL is that it matches only whole words. Thus, a search for "bla" will not match a column containing "blah," but a search for "bla*" will find it.

Preferred Approach

Based on performance considerations and the limitations of LIKE, MATCH AGAINST is the preferred choice for searching large datasets for keywords in multiple columns. It leverages the efficiency of full-text indexing to deliver fast and accurate results.

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