0

is the IN operator good for passing say 5000+ items into a large table thats properly indexed?

Basically im trying to get a list of Mp3's and checking if the song exists in our system by doing a simple name search.

(pseudocode) Select name from mp3s IN (array of mp3 names)

Faisal Abid
  • 8,900
  • 14
  • 59
  • 91

2 Answers2

3

Is fast

expr IN (value,...)

Returns 1 if expr is equal to any of the values in the IN list, else returns 0. If all values are constants, they are evaluated according to the type of expr and sorted. The search for the item then is done using a binary search. This means IN is very quick if the IN value list consists entirely of constants. Otherwise, type conversion takes place according to the rules described in Section 11.2, “Type Conversion in Expression Evaluation”, but applied to all the arguments.

refer : http://dev.mysql.com/doc/refman/5.0/en/comparison-operators.html#function_in refer : http://dev.mysql.com/doc/refman/5.0/en/type-conversion.html

For your case, check your max_allowed_packet to cater for huge string

ajreal
  • 46,720
  • 11
  • 89
  • 119
0

IN operator will cause performance issues. instead try

http://explainextended.com/2010/05/27/left-join-is-null-vs-not-in-vs-not-exists-nullable-columns/

Gajendra Bang
  • 3,593
  • 1
  • 27
  • 32