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

Hassan Schroeder hassan at webtuitive.com
Tue Nov 12 18:55:19 CST 2002


Paul Cowan wrote:

> Say you have the following tables:

> Recipe
> =======
> RecipeID    RecipeName
> --------    ----------
> 1           Welsh Rarebit
> 2           Cheese Omelette
> 3           Cheese sandwich

> RecipeIngredient
> =================
> RecipeID    IngredientName
> --------    --------------
> 1           Cheese
> 1           Bread
> 2           Cheese
> 2           Eggs
> 3           Cheese
> 3           Bread
> 3           Butter

> [1] SELECT RecipeID
> [2] FROM RecipeIngredient
> [3] WHERE
> [4]     IngredientName IN ('Cheese', 'Bread')

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

Is joining the two tables on RecipeID with a WHERE looking at one
table (whether one column or multiple) going to be more or less
processor-intensive than the regex filtering LIKE "%Cheese%Bread%"
on a SET column?

Any ideas? Other than "it depends" and "try it!" which may be what
this comes down to...  :-)

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

                           dream.  code.






More information about the thelist mailing list