[thelist] SQL : performance - separate table vs. (datatype) SET

[Hassan Schroeder]

jerryscannell at cox.net wrote:

> In general, it isn't a good idea to have "set" field types that
 > you will perform "like" type clauses.

> (2) an ingredients column using the SET datatype to include all
>       ingredients -- ingredients SET("milk","butter","eggs") -- and
>       search them via something like
>
>  WHERE ingredients LIKE "%milk%eggs%"
>
>  not sure that's how you search a set

Ah, since I posted that I discovered that rudy was right -- that's
*not* the way to search a set, you use the numeric mask, making the
example above

    WHERE ingredients = 5;

So using that presumably lighter approach, does your comment

 > The overhead relative to searching for such a data set is enormous.

still apply?

And if so, if SETs are inherently inefficient, why do they exist?
What problem were they created to solve? When *should* they be used?
Inquiring minds want to know!

TIA!
--
Hassan Schroeder ----------------------------- hassan at webtuitive.com
Webtuitive Design ===  (+1) 408-938-0567   === http://webtuitive.com

                           dream.  code.