Menu
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%'; 
668
Also LIKE is much faster than REGEXP.
Yeah, it probably would be a tiny bit faster because standard-SQL LIKE is a simpler comparison operation than a full-on regex parser. However, in real terms both are really slow, because neither can use indices. ( LIKE can use an index if the match string doesn't start with a wildcard, but that's not the case here.)
While neither of these operations (suffix matching) could leverage table indices, the LIKE operator ran 5-6 times faster than the RLIKE / REGEXP operator to find the same data.
Basically, LIKE does very simple wildcard matches, and REGEX is capable of very complicated wildcard matches. In fact, regular expressions ( REGEX ) are so capable that they are [1] a whole study in themselves [2] an easy way to introduce very subtle bugs.
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.
176
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
