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,507
  • 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
  • 20,148
  • 51
  • 149
  • 254
-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,968
  • 18
  • 26