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

r937 at interlog.com r937 at interlog.com
Tue Nov 12 21:11:01 CST 2002

>> Note that you'd probably want a seperate 'ingredients' table,
> OK, that's kind of a variation on my approach #1 -- but thats
> the question: which is likely to be the most efficient?

hassan, paul is very much on the right track

sets aren't bad per se, but extremely limited in application

also, one must strive not only for efficiency but also flexibility

> (1) a separate table of ingredients, with the recipe id as a key
>       and pseudo-boolean values for each ingredient column -- like
>       milk ENUM("false","true") -- and search with something like
> WHERE milk = "true" AND eggs = "true"

this will require changing your table definition whenever
new ingredients are added that you hadn't thought of

> (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

i've not used mysql sets myself, but i understand them to be a form
of domain, in that the allowable values are predefined, but you can still
fool around with their numbers...

i don't really like the way mysql has explained how sets work,
and not because they don't explain them well, but because they do


More information about the thelist mailing list