0

Is there any performance difference between the following?

NAME LIKE '%EXPRESSION_1%' 
OR NAME LIKE '%EXPRESSION_2%'
...
OR NAME LIKE '%EXPRESSION_N%'

VS

NAME IN (ACTUAL_VALUE_1,ACTUAL_VALUE_2,.., ACTUAL_VALUE_N)
Rahul Tripathi
  • 168,305
  • 31
  • 280
  • 331
Divs
  • 1,578
  • 2
  • 24
  • 51
  • http://stackoverflow.com/questions/782915/mysql-or-vs-in-performance – DonCallisto Feb 22 '16 at 14:39
  • 3
    Those two condition aren't the same thing. You can't compare them. To find out which is more efficient, just check the execution plan. –  Feb 22 '16 at 14:39
  • LIKE command slow than equal. IN is working look like equal with OR. – mkysoft Feb 22 '16 at 14:41
  • 1
    You trying to compare two different things ... as Like '%EXPRESSION_1%' will give you everything that contains %EXPRESSION_1% and IN (EXPRESSION_1) will give you just exact data ... – Veljko89 Feb 22 '16 at 14:42

3 Answers3

2

The IN version is potentially much, much faster.

The two versions do not do the same thing. But, if either meets your needs, the IN version can take advantage of an index on NAME. The LIKE version cannot, because the pattern starts with a wildcard.

You could write this as:

WHERE NAME LIKE 'EXPRESSION_%'

If this meets your needs, it can also take advantage of an index on NAME.

Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
1

You can simply try to use 

NAME LIKE '%EXPRESSION_%'

As far as the performance is concerned then IN is comparatively faster than OR. Also you can confirm the performance using the execution plan of your two queries.

Also as commented above, the two queries which you are showing are different.

The first query:

NAME LIKE '%EXPRESSION_1%' 
OR NAME LIKE '%EXPRESSION_2%'
...
OR NAME LIKE '%EXPRESSION_N%'

will try to fetch the result which has sample data like

EXPRESSION_123
XXXEXPRESSION_1234
EXPRESSION_2323
EXPRESSION_2......

whereas your second query will match the records which are exactly matching to

ACTUAL_VALUE_1,ACTUAL_VALUE_2.....
Rahul Tripathi
  • 168,305
  • 31
  • 280
  • 331
  • 1
    I think it's nescessary to mention that LIKE clause can easily invalidate indexing on the column, especially when it's wildcarded on both sides. Thus, performance lost on executing LIKE instead of strict comparison might be so high, that comparing OR and IN is meaningless in terms of optimization, in this case – Wintermute Feb 22 '16 at 14:45
0

If you are using variable expression which can be change according to the given parameter. Then use of

declare @Expression1 varchar(50)
Set @Expression2 = '%'+ @Expression1 +'%'


    NAME LIKE @Expression2

so whatever parameter will come in @Expression1 it will automatically take care of it.

Jai Prak
  • 2,855
  • 4
  • 29
  • 37