[Vtigercrm-developers] vtiger slowing down (a lot)

[Tom Brown]

Ruben,

The problem is the listview "ORDER BY vtiger_crmentity.modifiedtime DESC".
Each time the listview is opened, it will sort all 409k records, because
there is no "filter".

You have 2 options:

1. Change the ORDER BY to be "vtiger_crmentity.crmid ASC". This is set in
modules/Vtiger/models/Listview.php
Your query_time will drop to 1-2s.

* This method will also change the Related List sort order. If the problem
is only for Leads, you can just change the sort for leads to avoid related
list sort change.

2. Update the list "All" to have a condition (or create a new list and make
it a default for all users). The condition should filter out 95% of the
records i.e "modifiedtime is last 90 days".
Having the default list with a condition, will load a lot faster (1-2s).

Hope this helps





---

*Tom Brown* / Development Manager
+1 (818) 495-5557 / tbrown at vtexperts.com

*VTExperts*
vtexperts.com

The information transmitted, including attachments, is intended only for
the person(s) or entity to which it is addressed and may contain
confidential and/or privileged material. Any review, retransmission,
dissemination or other use of, or taking of any action in reliance upon
this information by persons or entities other than the intended recipient
is prohibited. If you received this in error, please contact the sender and
destroy any copies of this information.


On Wed, Sep 28, 2022 at 2:35 PM Rubén A. Estrada Orozco <rulotec1 at gmail.com>
wrote:

> Hi everyone,
>
> In cases where a  vtiger database gets big over time, queries start to
> take longer and longer. In some cases the database gets big because of junk
> records. In those cases one might want to delete those records to improve
> performance.
> In a recent case I noticed that a query for loading the detail view of the
> Leads module was taking aprox 10 seconds with ~409k records. Here is what I
> got from the slow query log (Note that the rows being examined is ~3.6
> million):
>
> # Query_time: 10.803175  Lock_time: 0.000091  Rows_sent: 115
>  Rows_examined: 3593722
> # Rows_affected: 0  Bytes_sent: 19074
> SET timestamp = 1664300474;
> SELECT
>   vtiger_crmentity.smownerid,
>   vtiger_leaddetails.leadsource,
>   vtiger_crmentity.description,
>   vtiger_crmentity.createdtime,
>   vtiger_crmentity.modifiedtime,
>   vtiger_leaddetails.leadstatus,
>   vtiger_leaddetails.firstname,
>   vtiger_leaddetails.lastname,
>   vtiger_leadaddress.phone,
>   vtiger_leaddetails.email,
>   vtiger_leadscf.cf_999,
>   vtiger_leadscf.cf_927,
>   vtiger_leadscf.cf_1090,
>   vtiger_leadscf.cf_1110,
>   vtiger_leaddetails.leadid,
>   vtiger_crmentity_user_field.starred
> FROM
>   vtiger_leaddetails
>   INNER JOIN vtiger_crmentity ON vtiger_leaddetails.leadid =
> 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
>   INNER JOIN vtiger_leadaddress ON vtiger_leaddetails.leadid =
> vtiger_leadaddress.leadaddressid
>   INNER JOIN vtiger_leadscf ON vtiger_leaddetails.leadid =
> vtiger_leadscf.leadid
>   LEFT JOIN vtiger_crmentity_user_field ON vtiger_leaddetails.leadid =
> vtiger_crmentity_user_field.recordid
>   AND vtiger_crmentity_user_field.userid = 107
>   INNER JOIN vt_tmp_u107_t7 vt_tmp_u107_t7 ON vt_tmp_u107_t7.id =
> vtiger_crmentity.smownerid
> WHERE
>   vtiger_crmentity.deleted = 0
>   and vtiger_leaddetails.converted = 0
>   AND vtiger_leaddetails.leadid > 0
> ORDER BY
>   vtiger_crmentity.modifiedtime DESC
> LIMIT
>   0, 115;
>
> So I have 2 questions:
>
> 1) If I decide to delete records, is it enough to  just set
> vtiger_crmentity.deleted = 1. Or should I entirely delete the records from
> the database? If so, how?
> 2) The database I'm dealing with has plenty of RAM available and I don't
> think that's an issue. So, is there a way to optimize the queries to get
> better speeds? I can't get an "Explain" from the query because I get an
> error saying that the table vt_tmp_u107_t7 does not exist.
>
> I'll appreciate any help.
>
> Saludos
>
> Rubén
> _______________________________________________
> http://www.vtiger.com/
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.vtigercrm.com/pipermail/vtigercrm-developers/attachments/20220929/e23df805/attachment.html>