[thelist] SQL : performance - separate table vs. (datatype) SET
jerryscannell at cox.net
jerryscannell at cox.net
Wed Nov 13 09:10:01 CST 2002
In general, it isn't a good idea to have "set" field types that you will perform "like" type clauses. The overhead relative to searching for such a data set is enormous. There are instances where such a lookup can actually bring down a web server. Be very careful if you use that kind of field type and select statement.
If you have to use "like", then try to avoid the "%xxx%" format as the database engine has to browse every character of every field looking for a starting point, never mind having to compare the rest. If you absolutely have to do it, then do it in a stored procedure so the bulk of the work is being done on the back end.
Jerry
Subset of the reply:
(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
More information about the thelist
mailing list