We recently upgraded MySQL from version 5.7 to 8.0.41. Post-upgrade, we have observed a noticeable improvement in overall query performance. However, we are encountering a significant performance issue, specifically when accessing list views in modules such as Leads, Clients and Cases, particularly when the views involve custom fields or custom tables.
The issue arises when filtering or sorting on a column from a custom table. In such cases, the query execution time increases substantially. For instance, the following query appears in the slow query log:
# Query_time: 11.155992 Lock_time: 0.000007 Rows_sent: 41 Rows_examined: 3,238,517
SET timestamp=1744089989;
SELECT leads.id, leads_cstm.last_call_date_c leads_cstm__last_call_date_c
FROM leads
LEFT JOIN leads_cstm leads_cstm ON leads_cstm.id_c = leads.id
WHERE leads.deleted = 0
ORDER BY leads_cstm.last_call_date_c DESC, leads_cstm.id_c DESC
LIMIT 41;
We understand that the performance degradation is primarily due to the LEFT JOIN and the ORDER BY clause on a column from the custom table (leads_cstm). Rewriting the query using an INNER JOIN significantly improves performance. However, since this query is auto-generated by SugarCRM when a user clicks a column header in the list view, direct modification is not feasible.
We also came across a MySQL 8.x recommendation suggesting the explicit use of table aliases with the AS keyword to potentially improve query performance and clarity:
SELECT leads.id, leads_cstm.last_call_date_c AS leads_cstm__last_call_date_c
FROM leads AS leads
LEFT JOIN leads_cstm AS leads_cstm ON leads_cstm.id_c = leads.id
WHERE leads.deleted = 0
ORDER BY leads_cstm.last_call_date_c DESC, leads_cstm.id_c DESC
LIMIT 41;
We are looking for any potential optimizations at the MySQL configuration level, query/index optimization, or SugarCRM application level that could help mitigate this performance issue, particularly for list views involving custom fields or sorting on custom tables.
Any insights or recommendations would be greatly appreciated.
