What's better? mysql LIKE or REGEXP?

Question

I have a name named_scope with LIKE and NOT LIKE in the same expression. Is it faster in terms of performance compared to using REGEXP?

named_scope :order_search, :order => "name LIKE '%urgent%' AND name NOT LIKE '%not urgent%' DESC, created_at DESC" 

Answer 1

This is a millisecond difference, imperceptible. If you are not dealing with an extremely large amount of traffic, it does not matter.

If you are having performance issues with your approach, this is probably because there are a lot of rows in your table. Then the neck of the bottle is not LIKE (and MySQL also supports REGEXP), but your table structure.

In any case, you should read the MySQL and MySQL Indexes full-text search .

Answer 2

As far as I know, MySQL LIKE will use Boyer-Moore in this case, so there might be a slight advantage for LIKE .

This will be negligible compared to using a trigger that would save the entire expression in the is_urgent field. Then you can add an index to (is_urgent, created_at) .

Answer 3

your request did not leave a large request, it could not use an index therefore similar and regular expressions are the same