SitePoint Sponsor

User Tag List

Results 1 to 3 of 3

Hybrid View

  1. #1
    SitePoint Enthusiast
    Join Date
    Mar 2002
    Location
    Italy
    Posts
    98
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Rebuilding indexes

    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

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,015
    Mentioned
    53 Post(s)
    Tagged
    2 Thread(s)
    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
    Quote Originally Posted by 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
    r937.com | rudy.ca | Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    SitePoint Enthusiast
    Join Date
    Mar 2002
    Location
    Italy
    Posts
    98
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •