[Vtigercrm-developers] Calendar Performance?

[Alan Lord]

I'm going through various bits of vtiger fixing the areas where it 
assumes that the Users module always has first_name,last_name as it's 
entityfield setting.

I have a fairly large database:

vtiger_activity has ~1.7million rows
vtiger_crmentity has ~2.7million rows
vtiger_users has 195 rows


When I'm in the ListView of the Calendar and try to sort by the Assigned 
To column I get a query like this:

> SELECT vtiger_activity.status, CASE WHEN (vtiger_activity.status not like '') THEN vtiger_activity.status ELSE vtiger_activity.eventstatus END AS status, vtiger_activity.activitytype, vtiger_activity.subject, vtiger_seactivityrel.crmid, vtiger_activity.date_start, vtiger_activity.time_start, vtiger_activity.due_date, vtiger_activity.time_end, vtiger_crmentity.smownerid, vtiger_activity.activityid, vtiger_activity.visibility FROM vtiger_activity  INNER JOIN vtiger_crmentity ON vtiger_activity.activityid = vtiger_crmentity.crmid LEFT JOIN vtiger_seactivityrel ON vtiger_activity.activityid = vtiger_seactivityrel.activityid LEFT JOIN vtiger_users ON vtiger_crmentity.smownerid = vtiger_users.id LEFT JOIN vtiger_groups ON vtiger_crmentity.smownerid = vtiger_groups.groupid  WHERE vtiger_crmentity.deleted=0 AND ( vtiger_activity.activitytype <> 'Emails')  ORDER BY COALESCE(vtiger_users.user_name, vtiger_groups.groupname) ASC LIMIT 0,21

It doesn't really work. The ORDER BY COALESCE() requires about 1.8m rows 
to be copied into a temporary table, then coalesced incase there's a 
group rather than a user, then ordered...

Can anyone think of a better way to query and sort on this size of 
database?

The alternative is I will remove the sort by feature of this column 
because it's just taking too much time/resource.

Cheers

Al