INSTR vs. LIKE in MySQL: Which String Matching Method Is Faster?

Performance Analysis: INSTR vs LIKE String Matching in MySQL

When testing for the existence of a string within a MySQL column of variable character length, a question arises regarding the optimal method to use for efficiency and speed. Two common approaches are:

1. INSTR(columnname, 'mystring' ) > 0
2. columnname LIKE '%mystring%'

Performance Evaluation

In terms of speed, our tests revealed a surprising result: both INSTR and LIKE perform identically when checking for substring existence. For instance:

INSTR(Name,'search') > 0
LIKE '%search%'

Both queries scanned the entire table and took approximately 5.54 seconds to complete. However, when performing a prefix search on an indexed column, the LIKE operator with only a suffix wildcard outperforms INSTR significantly:

LIKE 'search%'

This optimized prefix search took only 3.88 seconds.

Beyond INSTR and LIKE

In addition to INSTR and LIKE, MySQL also supports FULLTEXT searches for improved performance. However, these searches are most efficient when the table column is indexed with FULLTEXT and the string you're searching for appears near the beginning of the column.

Considerations

In practice, the choice between INSTR and LIKE depends on the specific use case:

• For full-table scans, both INSTR and LIKE perform similarly.
• For prefix searches on indexed columns, LIKE with a suffix wildcard is faster.
• For more complex or sophisticated search requirements, FULLTEXT searches should be considered.

The above is the detailed content of INSTR vs. LIKE in MySQL: Which String Matching Method Is Faster?. For more information, please follow other related articles on the PHP Chinese website!