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

[nilay khatri]

 Can I remove the "ORDER BY vtiger_crmentity.modifiedtime DESC" clause
globally on vtiger 7.1?

What other field would you then sort on ?

On Thu, Jul 5, 2018 at 6:04 PM Alan Lord <alanslists at gmail.com> wrote:

> 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
>
>
> _______________________________________________
> http://www.vtiger.com/
>
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.vtigercrm.com/pipermail/vtigercrm-developers/attachments/20180705/5a1d8e8b/attachment.html>