MySQL | REGEXP VS Like

mysql regex database-performance
Vardan Gupta picture Vardan Gupta · May 20, 2013 · Viewed 26.3k times · Source

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 now 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%';

Answer

draxxxeus picture draxxxeus · May 20, 2013

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.

Related questions

How to do a regular expression replace in MySQL?

I have a table with ~500k rows; varchar(255) UTF8 column filename contains a file name; I'm trying to strip out various strange characters out of the filename - thought I'd use a character class: [^a-zA-Z0-9()_ .\-] Now, is there …

Read More
How to regex in a MySQL query

I have a simple task where I need to search a record starting with string characters and a single digit after them. What I'm trying is this SELECT trecord FROM `tbl` WHERE (trecord LIKE 'ALA[d]%') And SELECT trecord …

Read More
MySQL SELECT LIKE or REGEXP to match multiple words in one record

The field table.name contains 'Stylus Photo 2100' and with the following query SELECT `name` FROM `table` WHERE `name` LIKE '%Stylus 2100%' I get no results. Of course i would if i searched SELECT `name` FROM `table` WHERE `name` …

Read More