INSTR vs. LIKE in MySQL: Which is More Efficient for String Searching?

Performance Comparison: INSTR vs. LIKE

When it comes to searching for a string within a MySQL column of type 'varchar', 'text', 'blob', etc., which method is more efficient: INSTR(columnname, 'mystring') > 0 or columnname LIKE '%mystring%'?

The Answer

FULLTEXT searches offer the best performance. However, when comparing INSTR and LIKE, both methods perform identically in regular non-indexed full-table scans.

Detailed Comparison

In tests conducted by the author, INSTR and LIKE performed equally. However, when performing a prefix search on an indexed column using LIKE with only a suffix wildcard (e.g., columnname LIKE 'search%'), it significantly outperforms INSTR.

Implementation

Both INSTR and LIKE scan the column from left to right, comparing the search string with the stored data. INSTR searches for an exact match, while LIKE allows wildcard characters (% and _) for partial matches.

Recommendation

For indexed columns where prefix matching is not required, INSTR and LIKE are both viable options with similar performance. However, when dealing with non-indexed tables or searching for suffixes only, LIKE with a suffix wildcard is recommended for faster results.

The above is the detailed content of INSTR vs. LIKE in MySQL: Which is More Efficient for String Searching?. For more information, please follow other related articles on the PHP Chinese website!