[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