CODEWITHSUNDEEP
mysqlruby-on-railssql-like
cancelledout
cancelledout

Reputation: 528

Which is better? mysql's LIKE or REGEXP?

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"

Upvotes: 6

Views: 2661

Answers (3)

Neo
Neo

Reputation: 5463

your query is not left most query, it could not use index so the like and regexp are same

Upvotes: 1

fx_
fx_

Reputation: 1932

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.

Upvotes: 5

Denis de Bernardy
Denis de Bernardy

Reputation: 78551

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).

Upvotes: 2

Related Questions