3

Possible Duplicate:
MySQL LIKE vs LOCATE

I need to write a Mysql query which chooses rows based on a string in a column. Say the column is named TestColumn. I need to choose all rows which contain 'stackoverflow' anywhere in the column. Would it better to use

Select * from testtable where TestColumn like '%stackoverflow%'

or

Select * from testtable where Locate('stackoverflow', TestColumn)>0

Which would result in a better performance? Thanks in advance.

Community
  • 1
  • 1
Maxim Dsouza
  • 1,497
  • 3
  • 19
  • 32
  • Has been asked: http://stackoverflow.com/questions/7499438/mysql-like-vs-locate – K2xL Oct 24 '11 at 18:44
  • 1
    Both prevent the use of indexes, so they're going to be painfully slow on large tables either way. – Marc B Oct 24 '11 at 18:44

2 Answers2

0

This is really a problem for LIKE. LOCATE should be used when the position of the substring is important.

ewok
  • 18,668
  • 45
  • 132
  • 245
-1

% is better than a function call. % will use full text index if it is existing. It is not the case with the function call.

Vivek Viswanathan
  • 1,917
  • 18
  • 25