1

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?

Yap Kai Lun Leon
  • 316
  • 1
  • 9
  • 1
    Try it yourself and benchmark the results? You *should* find that, with an index on `name`, the first approach will be much faster; whereas if `name` is not indexed, the difference is probably insignificant (with the second approach potentially faster, depending on MySQL's pattern matching optimisations). – eggyal Oct 07 '13 at 07:51
  • How would it affect when the parameter increase to 10 or 100? will LIKE OR combination always faster than regexp | ? Or the difference is insignificant due to the LIKE is much faster than REGEXP. – Yap Kai Lun Leon Oct 09 '13 at 02:41

2 Answers2

1

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.

Jhanvi
  • 5,069
  • 8
  • 32
  • 41
0

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.

Pandepic
  • 715
  • 1
  • 6
  • 9