MySQL | REGEXP VS Like

Question

I have a table CANDIDATE in my db which is running under MySQL 5.5 and I am trying to get rows from table where RAM is contains in firstname, so I can run below two queries, but I would like to know which query we should use for long term with respect to optimization.

SELECT * FROM CANDIDATE c WHERE firstname REGEXP 'ram';
SELECT * FROM CANDIDATE c WHERE firstname LIKE'%ram%';

Jahveh

Just to note, if you are looking for instances of a character / character set, use a regex. – Jacquesjacquet

@JustinE - Before version 8.0, utf8 multi-byte characters were not handled correctly by REGEXP. – Zane

Answer 1

LIKE vs REGEXP in MySQL

• Single condition: LIKE is faster
• Multiple conditions: REGEXP is faster

Example

A real-world example could be: finding all rows containing escaped carriage returns of CRLF, LF, or CR.

Single condition

SELECT * FROM comments WHERE text LIKE '%\\\\n%'; 🚀 Faster
SELECT * FROM comments WHERE text REGEXP '\\\\n'; 🐢 Slower

Multiple conditions

SELECT * FROM comments
WHERE text LIKE '%\\\\r\\\n%'
OR text LIKE '%\\\\n%'
OR text LIKE '%\\\\r%'; 🐢 Slower

SELECT * FROM comments
WHERE text REGEXP '((\\\\r\\\\n)|(\\\\(n|r)))'; 🚀 Faster

Conclusion

Use LIKE for single condition queries, and REGEXP for multiple condition queries.

Poverty

Answer 2

I've tried it out on MySQL 8.0.13 and compared LIKE vs REGEXP on a table with 1M+ rows on a column with an index:

SELECT * FROM table WHERE column LIKE '%foobar%';

Query took 10.0418 seconds.

SELECT * FROM table WHERE REGEXP_LIKE(column, 'foobar');

Query took 11.0742 seconds.

LIKE performance is faster. If you can get away with using it instead of REGEXP, do it.

Limacine

Answer 3

REGEXP and LIKE are used to totally different cases.

LIKE is used to add wildcards to a string whereas REGEXP is used to match an attribute with Regular Expressions.

In your case a firstname is more likely to be matched using LIKE than REGEXP and hence, it will be more optimized.

Picard

Answer 4

If you can use LIKE instead of REGEXP, use LIKE

Burning

Answer 5

Better Use of LIKE Query instead of REGEXP if you are not sure about value.

Also LIKE is much faster than REGEXP.

Turnstile

Could you provide any information supporting the statement "LIKE is much faster than REGEXP" – Radiance