Are your custom fields out of control?

FieldManager 1.0 for MySQL

Every SugarCRM installation I have ever worked on has had the same problem.  Custom fields that are out of control.  There are several problems with Studio when it comes to custom fields.  It really doesnt tailor the field to the data it will contain.

1. The size option for VARCHARs defaults to 255, for dropdowns it's 100 even though we know the exact size of the data that will fill it.  
2. It doesnt allow for CHARS.  
3. Fields that are filled from formulas should have the option to be non-db fields as they are usuaally for display only.  
4. Too many people use TEXT when a VARCHAR would work fine.  
5. Fields are created and then never used or fields are abandoned in favor of something new.
6. Fields are created and then are used for a datatype they are not intended for like using a VARCHAR to hold a date or numeric.
7. Fields are added and then so rarely used it begs for a redesign.  Maybe there is a checkbox that has never been checked or a dropdown that everyone lets default.

You might not thing that it matters if aVARCHAR is 50 charactoers long or 255 characters but it does.  In MySQL, temporary tables and MEMORY tables store a VARCHAR column as a fixed-length CHAR column, padded out to its maximum length.  If you design VARCHAR columns much larger than the greatest size you need, you will consume more memory than you have to. This affects cache efficiency, sorting speed, etc.  So that means that if your SELECT query requires a temporary table to sort or group then all those VARCHAR(255+) will bite you in the ass performance-wise.

Now, going through your custom fields is a pain in the butt because you have to go to 2 or 3 places to get all the information you need to make a descision about how to optimize your field.  With FieldManager all that information is on one screen. 

As of yet, FieldManager doesnt actually alter or change anything.  Its just a report.  Future versions will ba able to alter the size of a VARCHAR or remove a field entirely amoung other things.  I hope to have that version done by summer. 

So this is the report Field Manager creates and the basics of the layout...

There are 2 checkboxes under the Module selector there at the top.  They allow you to skip indexing the reports and Workflow/PMSE/PDF/Templates as those things do take a bit of time and are not always needed.  Depending on your machine you might have to run the report a few modules at a time as it does run quite a few queries for each and every field.  Also, to index Reports I create a temporary table so the MySQL user you have must have permission to do that.  I have not found an install where that isnt already true but just in case.

So here is a vidoeo that goes over how I use the tool, below that is a link to th ecode and a loadable module.

The files are available here