[Vtigercrm-developers] ORDER BY modifiedtime slow...

[Alan Lord]

Development migration vtiger system (5.4.0 - 7.1.0), fast server, SSDs, 
loads of RAM.

Loading lists is really slow on a fairly large database (crmentity ~5.5m 
rows).

For example; loading a page of Accounts uses the following query:

SELECT vtiger_account.account_no, vtiger_account.accountname, 
vtiger_account.website, vtiger_account.phone, 
vtiger_crmentity.smownerid, vtiger_account.accountid, 
vtiger_crmentity_user_field.starred FROM vtiger_account INNER JOIN 
vtiger_crmentity ON vtiger_account.accountid = vtiger_crmentity.crmid 
LEFT JOIN vtiger_users ON vtiger_crmentity.smownerid = vtiger_users.id 
LEFT JOIN vtiger_groups ON vtiger_crmentity.smownerid = 
vtiger_groups.groupid LEFT JOIN vtiger_crmentity_user_field ON 
vtiger_account.accountid = vtiger_crmentity_user_field.recordid AND 
vtiger_crmentity_user_field.userid=1 WHERE vtiger_crmentity.deleted=0 
AND vtiger_account.accountid > 0 ORDER BY vtiger_crmentity.modifiedtime 
DESC LIMIT 120,21

This takes between 3 & 5 seconds.

If I drop the ORDER BY modifiedtime it's fast, ~0.01s

I tried adding an index on the modifiedtime column but this made no 
difference - MySQL still needs to use filesort and a temporary table.

Then I dropped that index and created one combining crmid and 
modifiedtime - not much better either.

So, two questions:

1. Any DBAs know how to speed this up (if possible)
2. Can I remove the "ORDER BY vtiger_crmentity.modifiedtime DESC" clause 
globally on vtiger 7.1?

Cheers

Al