INSTR vs. LIKE in MySQL: Which String Matching Function is Faster?

Performance Comparison of INSTR and LIKE for MySQL String Matching

In MySQL, when searching for a string within a column, two common methods are INSTR and LIKE. Which one is faster and more efficient?

INSTR vs LIKE

INSTR(columnname, 'mystring') > 0 returns true if 'mystring' is found within columnname, while columnname LIKE '%mystring%' returns true if 'mystring' is found anywhere in columnname. Both INSTR and LIKE are supported for string columns of types 'varchar', 'text', 'blob', etc.

Performance Tests

In a test, counting the occurrences of 'search' in a table with over 40,000 rows, both INSTR and LIKE performed identically, taking approximately 5.54 seconds. However, when using LIKE with only a suffix wildcard, such as 'search%', the query was significantly faster, taking just 3.88 seconds.

Full-Text Search

Although not specifically mentioned in the original question, it's worth noting that FULLTEXT searches are generally faster for text-based queries. However, they require the use of specialized indexes and are not always suitable for all scenarios.

Conclusion

For full-table scans, INSTR and LIKE with both prefix and suffix wildcards perform similarly. However, when searching for a string at the beginning of a text column, LIKE with a suffix wildcard becomes much more efficient. In cases where performance is critical, considering the use of full-text search may provide the best results.

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