Which is better? mysql's LIKE or REGEXP?

Question

I have this named_scope with a LIKE and NOT LIKE in one statement. Is this faster in terms of execution compared to using REGEXP?

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

Answer 1

It's milliseconds difference, unnoticable. Unless you're dealing with an extremely high amount of traffic, it won't matter.

If you're experiencing performance issues with your approach, it's likely because your table has a large amount of rows. The bottle-neck isn't LIKE then (and MySQL also supports REGEXP) but your table structure.

In any case, you should read up on MySQL Indexes and MySQL Fulltext Search.

Answer 2

In so far as I'm aware, MySQL's LIKE will use Boyer–Moore in this case, so there might be a slight advantage for LIKE.

It'll be negligible compared to using a trigger that would store the whole expression in an is_urgent field however. You could then add an index on (is_urgent, created_at).