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%';
A real-world example could be: finding all rows containing escaped carriage returns of CRLF, LF, or CR.
SELECT * FROM comments WHERE text LIKE '%\\\\n%'; 🚀 Faster
SELECT * FROM comments WHERE text REGEXP '\\\\n'; 🐢 Slower
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
Use LIKE for single condition queries, and REGEXP for multiple condition queries.
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.
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.
If you can use LIKE instead of REGEXP, use LIKE
Better Use of LIKE Query instead of REGEXP if you are not sure about value.
Also LIKE is much faster than REGEXP.
