Mysql LIKE IN()?

sql

regexp

performance

byNikita Barsukov·Oct 10, 2024

In MySQL, executing a query resembling LIKE IN() involves stringing together several LIKE conditions using OR, or adopting REGEXP for a cleaner, succinct solution. Here's an effortless REGEXP instance matching 'pattern1', 'pattern2', and 'pattern3':

SELECT * FROM table_name WHERE column_name REGEXP 'pattern1|pattern2|pattern3';

This deft line of code supplants several LIKE conditions and the OR operator, enhancing readability and manageability of the query.

Building on these fundamentals, this answer will dive deeper into the intricacies of MySQL pattern matching, performance tuning, pitfall avoidance, complex pattern searching, and more.

The REGEXP Edge in MySQL

When it comes to SQL's versatility, pattern matching is the linchpin. You can harness the power of REGEXP to coalesce patterns, thereby emulating multiple LIKE conditions. However, because REGEXP can be more resource-demanding, always benchmark its use vis-à-vis LIKE operations.

Picking your Battle: REGEXP vs LIKE

Choosing between REGEXP and LIKE may boil down to performance considerations, particularly with sizeable datasets. A few pointers to consider for optimal performance:

Benchmark both implementations to discern which works best for your case.
Understand index usability since REGEXP doesn't usually benefit from indexes.
Determine the role of table size, data distribution, and pattern complexity.

Escaping REGEXP Quandaries

While powerful, MySQL's REGEXP has its peculiarities. Here are some strategies to enhance your usage:

Use anchors '^' and '$' to explicitly match the start and end of a value.
Combine NOT REGEXP with REGEXP for discerning pattern exclusions.
In complex scenarios, you may need to pair REGEXP with LIKE or NOT LIKE.

Assembling Intricate Pattern Searches

If REGEXP alone doesn't cut it, you can layer it:

Utilize CONCAT_WS in crafting complex searches akin to LIKE IN().
Implement % wildcard within LIKE for broad pattern matching.
In situations where REGEXP falters, revert to LIKE bound with OR.

Constructing Complex Queries Using Subqueries

In more intricate scenarios, a JOIN paired with a subquery might be your magic spell:

SELECT b.*
FROM books b
JOIN (SELECT 'JavaScript' AS pattern UNION ALL 
      SELECT 'Python' UNION ALL 
      SELECT 'Java') patterns
ON b.title LIKE CONCAT('%', patterns.pattern, '%');
-- Don't judge a book by its cover, judge it by its code! 😉

Such a method averts redundancy and ensures each book is listed once, despite multipattern matches.

Performance Profiling Using EXPLAIN

Performance assessment is paramount. Engage the EXPLAIN command to examine execution plans and tailor your patterns accordingly:

EXPLAIN SELECT * FROM books WHERE title REGEXP 'Java|Python|JavaScript';
-- EXPLAIN? Don’t panic, it’s only SQL! 😆

REGEXP: Not Always the Hero

While REGEXP is mighty, here are scenarios where you might want to consider other options:

When operating on indexed columns, LIKE with a trailing wildcard could leverage the index, unlike REGEXP.
If performance plummets significantly compared to using multiple LIKE conditions.