I have a question regarding the multi-select field search when query against a large table.
SugarCRM stores these multi-select fields in a format like the below:
When a user uses SugarCRM's filter search for say 'is any of' on the listview,
SugarCRM will generate a query with a WHERE like this:
'WHERE some_field LIKE '%^some_value_1^%';
For a large table of say a few million rows, this will cause SugarCRM to produce an incredibly slow table scan. Looking at some of the queries generated by SugarCRM's filter, I've seen queries running at greater than 30 minutes.
Has anyone (or SugarCRM) come up with a workable solution to this? Is there a plan to address this in later versions?