CODEWITHSUNDEEP
mysqlregexsql-like
Yap Kai Lun Leon
Yap Kai Lun Leon

Reputation: 326

Mysql LIKE vs REGEXP with 2 or more param

Which would be faster and better on large data?

SELECT * FROM 'table' where name LIKE 'micky%' OR name LIKE 'molly%'

SELECT * FROM 'table' where name REGEXP '(^micky | ^molly)'

When the parameters more than 10, will it make the slower one become faster?

Upvotes: 1

Views: 494

Answers (2)

Pandepic
Pandepic

Reputation: 765

MySQL regexp wont use an index, but LIKE will use an index if it's possible for it to do so. Because in this case your search doesn't begin with a wildcard, LIKE will use an index if it's available.

So the answer is that you should have an index on name if you're searching on it, and when you do then LIKE will be faster in this situation.

Upvotes: 0

Jhanvi
Jhanvi

Reputation: 5149

Speed of a query depends on the indexes of your database structure. If 'SELECT' query consists of the indexed parameters, then you should use 'LIKE' provided that wildcard is not the first character.But if there is no indexing in your structure then you can use either of them. It would be best to test the time taken in both scenarios.

Upvotes: 1

Related Questions