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

[Angelo Paglialonga]

Hi Ruben, I would suggest to just add indexes on columns for all tables of the slow module, it worked for me every time. 
All the best 

_______________

Angelo Paglialonga
Consulente per soluzioni CRM ad alto ROI.

Telefono: 3386077866
Web: https://www.angelopaglialonga.com


> Il giorno 29 set 2022, alle ore 14:20, Tom Brown <support at vtexperts.com> ha scritto:
> 
> 
> 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/
> _______________________________________________
> http://www.vtiger.com/
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.vtigercrm.com/pipermail/vtigercrm-developers/attachments/20220929/d1ff71eb/attachment-0001.html>