Rebuilding indexes

[frodo]

Hi,
I just changed the format of a column from tinyint(1) to enum(‘0’, ‘1’), as this colum had an index on it, should I be rebuilding the index (drop and then create) or is MySql taking care of this?

Thanks,
Adrien

[r937]

an index on a column that has only two values will likely not be used at all, because it isn’t selective enough

may i ask why you thought it necessary to make that change?

from a performance point of view, it’s a wash, but from a portability point of view, you’ve locked yourself into a proprietary mysql format that is not supported by other database systems

also, there is a ~real~ big problem with enums that happen to have string values like ‘0’ and ‘1’, it is too easy to make a coding error and get the wrong value

@da manual

If you insert an invalid value into an ENUM (that is, a string not present in the list of allowed values), the empty string is inserted instead as a special error value. This string can be distinguished from a ``normal’’ empty string by the fact that this string has the numerical value 0.

It’s not advisable to define an ENUM column with enumeration values that look like numbers, because this can easily become confusing. For example, the following column has enumeration members with string values of ‘0’, ‘1’, and ‘2’, but numeric index values of 1, 2, and 3:

numbers ENUM(‘0’,‘1’,‘2’)

how easy is it, do you think, to write a php script which is sometimes just a wee bit lax in distinguishing between 0 and ‘0’? the difference here will kill ya

[frodo]

Hi r937,
Thanks for the tips. Unfortunately I had already done the switch, so when I read your post I switched over. I wished I had been more carful as a lot of my flags simply got f***ed! It took me a while to fix it all
Anyway: I’m glad I’ve learned something new!

Adrien