Re: [PHP4BETA] re:Enum type in MySQl

Philippe Verdy wrote: > So your WHERE clause is simply incorrect for what you want to do! > FIND_IN_SET('N','Y','N') is a constant expression that does not > depend on > any data row, and it will always return 2. It is avaluated before rows are > index-searched or scanned. But this coinstant integer value cannot be used > to compare to the field_name for which you want it to be 'N'. A little bit > confusing, no ? > > Why not simply writing: WHERE field_name='N' ? > > If you insist in using sets, you could alternatively write: WHERE > FIND_IN_SET(field_name, 'Y','N')=2, but it will be much less performant, as > it cannot use index search but only row scans to match the condition. > > This FIND_IN_SET() function has the same kind of uses as the SUBSTR() > DECODE() and NVL() functions in Oracle, or SUBSTRING(), ISNULL() in Sybase, > that can be used to sustitute one value by another, avoiding an additional > join to a dedicated table object. But this substitute function applied to > table columns should be avoided as much as possible in WHERE clauses because > this must be evaluated on row of matching subsets, and only AFTER these > subsets have been possibly searched through an index (this means that such a > function in a WHERE clause does not qualify the prodicate for index search, > and may produce full table scans if no other join or condition restricts its > evaluation). > > In fact, such functions have been primarily designed for inclusion in the > SELECT clause of a query, or in a SET clause of an UPDATE, but not as a good > way to qualify rows in a WHERE clause (the best way to qualify rows is by > equality condition, then by relational conditions, by IS NULL or IS NOT > NULL, or by IN(constant list). The use of NOT IN(constant list), or > difference condition is not qualifying in any index search (unless your DBMS > supports index on expression or VIEWS). Hey thanks for this, after reading this I am going back to just using a preset char(1) value of "Y" or "N" I really just wanted a kind of Boolean operation at which to test against, I am also having problems with the Default value not being set when a new record is created, maybe I also am confused on this issue. I guess I can always manually set these with variables and then just use Update, although this is where I ran in to trouble in the first place as it gets rather confusing and if the variable is not passed between the pages it will be empty. Thanks for your help Joseph H Blythe mailto:joe.blythe@binarylogic.com.au Web Developer Binary Logic 257 Churchill Road, Prospect Adelaide, South Australia, 5034 PH:(08)83420022 FAX:(08)83420155