While working on improving the performance of list views, the client asked as to enable sorting for a few specific fields and disable it for all others in Accounts and Contacts modules. We are using Sugar 8.0.0 with MySQL 5.7 and PHP 7.1.
In process of enabling sorting for those few specific fields, we made sure that they are indexed in the database by creating some additional indices for them.
The general problem is that the sorting on the list view is VERY slow. We compared the performance with another test instance that uses the same codebase, infrastructure and data and it turned out that adding indices to those few fields did not really bring tangible performance gains for sorting.
How is it possible that for ordering the list view by the Account.name field it takes 1-2 sec to sort whereas using another field from the primary module table (even if its indexed) causes sorting to take 30 sec ? We are aware of the fact that Sugar stock queries might be better optimized for some (commonly used) fields than others and also that the query optimizer might not use a given index for a given query, but still such poor performance baffles us.
As we studied both various docs and relevant community posts, we are aware of the following performance-focused improvements:
- if sorting is required on a custom field, move it from _cstm to the primary module table via metadata manipulations
- set up MySQL master-slave replication and use the Slave to serve Reports and ListViews
Can you advise what other approaches/solutions we can try to make the sorting work faster?