MySQL String Matching: INSTR vs. LIKE: Which is More Efficient?

MySQL Column String Matching: INSTR vs. LIKE

When searching for a substring within a MySQL column of type 'varchar', 'text', or 'blob', two common search methods arise: INSTR and LIKE. However, which approach is more efficient?

The question centers around whether INSTR, which uses the INSTR function to locate the position of a specified string, or LIKE, which utilizes pattern-matching with wildcards, is the more optimal choice.

Performance Comparison

Benchmarking reveals that for full-table scans, both INSTR and LIKE exhibit similar performance:

INSTR( columnname, 'mystring' ) > 0: 5.54 sec
columnname LIKE '%mystring%': 5.54 sec

However, a key distinction emerges when performing prefix searches on indexed columns:

Name LIKE 'search%': 3.88 sec

In this scenario, LIKE with only a suffix wildcard significantly outperforms INSTR.

Alternative: FULLTEXT Search

While both INSTR and LIKE prove useful for string matching, it's important to note that FULLTEXT searches excel in efficiency for substring searches. They leverage indexed word lists, enabling lightning-fast performance, especially when searching large columns. Consider employing FULLTEXT when speed is paramount.

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