Thread Details
  • State Not Answered
  • Replies 4 replies
  • Subscribers 367 subscribers
  • Views 1266 views
  • Users 0 members are here
Forum Actions

Pitfalls to Converting Dropdowns to the MySQL enum type

Kenneth Brill

I have run some tests on our production data and the results look promising.  I want to convert all my dropdowns to actual MySQL enum type from the current VarChar types.  I am rewriting the MySQL code in Sugar to support this so my QRRs and any New Dropdowns are created as mysql enum type instead of Varchars if I choose to do so..

There seems to be no other code changes needed.  All the queries, reports, widgets and logic hooks all work normally.

So the real question......

Does anyone know of any pitfalls that I am missing here?  In my tests (and we have A LOT of drop downs) the DB used much less memory and was a lot faster using the enum type over a varchar, sorting was the only pitfall I have seen yet but it was easily overcome.  Of course it shrank my databases by gigabytes as there is much less data actually stored in the tables.  I understand updates to the list will be very expensive but we only do that during maintenance windows anyway and there are ways around that if it gets too bad.

  1. To facilitate upgrades and have written a script to convert the fields back and forth.  To enums for use and back to varchars for upgrades, at least until SugarCRM supports it if ever.
  2. I don't bother with Stock fields, just custom ones.  Since both types of fields are code agnostic I can use them both side by side without any code changes to anything.
  3. I have a checkbox on the DropDown field definition screen that allows you to use the legacy (VarChar) format for any enum that you know will change a lot (like I have a field that chooses the trade show they are at and we add trade shows all the time, so that one I would like to remain a Varchar.
  4. I had to add logic to the List Editor so if you eliminate an item it pops up a 'remap' option to maintain data integrity.
  • 0

    This is a really interesting idea, which might benefit your specific installation because of maintenance window & less frequent changes. 

    Since it's been a while, was curious to know if you went ahead and deployed this change to production? How did it go?

  • 0 in reply to Neeraja P

    I have deployed it in a test environment.  it was far easier than I thought but I found a few pitfalls.

    1. I cant override the files that define the 'enum' field type creation page in studio (modules/DynamicFields/templates/Fields/*).  Its hard coded to use the stock files and I cant find a way around it.  I wanted a way to allow for 'old style' and 'new style' dropdowns.
      1. This is needed because there is a limit on the number of values allowed in the MySQL enum field type.  So if you have a field with 1000 values then you have to leave it alone with the old varchar style.
    2. Upgrades still fail on the QRR part as it tries to convert the columns back to varchars.   Still looking into that one.
    3. There is a sorting issue but I think I can solve that one.

    but the benefits were all that I was hoping for.  Some of my tables are 30-60% smaller, some of our canned filters are 30% faster and there was no change at all in the user experience with just 3 files altered in the whole code base.

  • 0 in reply to Kenneth Brill

    Thanks for the reply, good to know.

    On #1 and #2 - You can try exploring the "Custom Field Type" -https://support.sugarcrm.com/Documentation/Sugar_Developer/Sugar_Developer_Guide_10.0/Cookbook/Creating_Custom_Fields/#Defining_the_Sugar_Widget

    The custom field can just extend the "Enum" fields by default, so that this is upgrade-safe and you can leave the regular enum field as-is for varchar.

    And for #3 - yes you can just sort the enums values before updating the DB, so that wouldn't be an issue. 

  • 0 in reply to Neeraja P

    Yeah, but I didnt want it to be a custom field type, I wanted it to override the current enum type so you would have an interface to convert any of your current varchar enums into MySQL enum types.  Having that would make it upgrade safe bt only if there was an upgrade safe way to override that bit of code....

    In the end a custom field type would work but then I'd have to write code to convert any current custom enum field into this custom enum field and I wanted to avoid that bit of confusing interface.

    I think, maybe, in the end I could create this custom enum field that supports BOTH Varchar and MySQL enum fields and then have the installer go though and convert all current custom enum fields to this new custom field and then you can go into studio and choose which ones you want to convert to the MySQL Enum type.  That would work and be upgrade safe and would be the least confusing option available.