开发者

Large Table In operator MySql

开发者 https://www.devze.com 2023-01-25 17:55 出处:网络
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 syste开发者_Go百科m b

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 syste开发者_Go百科m by doing a simple name search.

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


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


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/

0

精彩评论

暂无评论...
验证码 换一张
取 消