Thread Details
  • State Not Answered
  • Replies 4 replies
  • Subscribers 369 subscribers
  • Views 1306 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.
Parents
  • 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.

Reply
  • 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.

Children