<html><head><meta http-equiv="content-type" content="text/html; charset=utf-8"></head><body dir="auto">Hi Ruben, I would suggest to just add indexes on columns for all tables of the slow module, it worked for me every time. <div>All the best <br><br><div dir="ltr"><span style="border-collapse: separate; "><div style="word-wrap: break-word; "><span style="border-collapse: separate; "><div style="word-wrap: break-word; "><span style="border-collapse: separate; "><div style="word-wrap: break-word; "><span style="border-collapse: separate; "><span style="border-collapse: separate; "><span style="border-collapse: separate; "><span style="border-collapse: separate; "><span style="border-collapse: separate; "><span style="border-collapse: separate; "><span style="border-collapse: separate; "><span style="border-collapse: separate; "><span style="border-collapse: separate; "><span style="border-collapse: separate; "><span style="border-collapse: separate; "><div style="word-wrap: break-word; "><span style="border-collapse: separate; "><div style="word-wrap: break-word; "><span style="border-collapse: separate; "><div style="word-wrap: break-word; "><span style="border-collapse: separate; "><div style="word-wrap: break-word; "><span style="border-collapse: separate; "><div style="background-color: rgba(255, 255, 255, 0); word-wrap: break-word; "><div class="ApplePlainTextBody" style="font-family: Helvetica; font-size: 14px; -webkit-text-size-adjust: auto;">_______________<br><br>Angelo Paglialonga<br>Consulente per soluzioni CRM ad alto ROI.<br><br>Telefono: 3386077866<br>Web: https://www.angelopaglialonga.com</div><div class="ApplePlainTextBody" style="font-family: Helvetica; font-size: 14px; -webkit-text-size-adjust: auto;"><br></div></div></span></div></span></div></span></div></span></div></span></span></span></span></span></span></span></span></span></span></span></div></span></div></span></div></span></div><div dir="ltr"><br><blockquote type="cite">Il giorno 29 set 2022, alle ore 14:20, Tom Brown <support@vtexperts.com> ha scritto:<br><br></blockquote></div><blockquote type="cite"><div dir="ltr"><div dir="ltr"><div class="gmail_default" style="font-family:verdana,sans-serif;color:#444444">Ruben,</div><div class="gmail_default" style="font-family:verdana,sans-serif;color:#444444"><br></div><div class="gmail_default" style="font-family:verdana,sans-serif;color:#444444">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".<br></div><div class="gmail_default" style="font-family:verdana,sans-serif;color:#444444"><br></div><div class="gmail_default" style="font-family:verdana,sans-serif;color:#444444">You have 2 options:</div><div class="gmail_default" style="font-family:verdana,sans-serif;color:#444444"><br></div><div class="gmail_default" style="font-family:verdana,sans-serif;color:#444444">1. Change the ORDER BY to be "vtiger_crmentity.crmid ASC". This is set in modules/Vtiger/models/Listview.php</div><div class="gmail_default" style="font-family:verdana,sans-serif;color:#444444">Your query_time will drop to 1-2s.</div><div class="gmail_default" style="font-family:verdana,sans-serif;color:#444444"><br></div><div class="gmail_default" style="font-family:verdana,sans-serif;color:#444444"></div><div class="gmail_default" style="font-family:verdana,sans-serif;color:#444444">* 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.</div><div class="gmail_default" style="font-family:verdana,sans-serif;color:#444444"><br></div><div class="gmail_default" style="font-family:verdana,sans-serif;color:#444444">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".</div><div class="gmail_default" style="font-family:verdana,sans-serif;color:#444444">Having the default list with a condition, will load a lot faster (1-2s).</div><div class="gmail_default" style="font-family:verdana,sans-serif;color:#444444"><br></div><div class="gmail_default" style="font-family:verdana,sans-serif;color:#444444">Hope this helps</div><div class="gmail_default" style="font-family:verdana,sans-serif;color:#444444"><br></div><div class="gmail_default" style="font-family:verdana,sans-serif;color:#444444"><br></div><div class="gmail_default" style="font-family:verdana,sans-serif;color:#444444"><br></div><div class="gmail_default" style="font-family:verdana,sans-serif;color:#444444"><br></div><div><div dir="ltr" class="gmail_signature" data-smartmail="gmail_signature"><div dir="ltr"><div><font face="verdana, sans-serif"><br></font></div><div><font face="verdana, sans-serif">---<br></font></div><div><font face="verdana, sans-serif"><br></font></div><div><font face="verdana, sans-serif"><b>Tom Brown</b> / Development Manager</font></div><div><font face="verdana, sans-serif">+1 (818) 495-5557 / <a href="mailto:tbrown@vtexperts.com" target="_blank">tbrown@vtexperts.com</a></font></div><div><font face="verdana, sans-serif"><br></font></div><div><font face="verdana, sans-serif"><b>VTExperts</b></font></div><div><font face="verdana, sans-serif"><a href="https://vtexperts.com" target="_blank">vtexperts.com</a></font></div><div><font face="verdana, sans-serif" size="1"><br></font></div><div><font face="verdana, sans-serif" size="1">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.</font></div></div></div></div><br></div><br><div class="gmail_quote"><div dir="ltr" class="gmail_attr">On Wed, Sep 28, 2022 at 2:35 PM Rubén A. Estrada Orozco <<a href="mailto:rulotec1@gmail.com">rulotec1@gmail.com</a>> wrote:<br></div><blockquote class="gmail_quote" style="margin:0px 0px 0px 0.8ex;border-left:1px solid rgb(204,204,204);padding-left:1ex"><div dir="ltr">Hi everyone,<div><br></div><div>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.</div><div>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):</div><div><br></div><div># Query_time: 10.803175 Lock_time: 0.000091 Rows_sent: 115 Rows_examined: 3593722<br># Rows_affected: 0 Bytes_sent: 19074<br>SET timestamp = 1664300474;<br>SELECT <br> vtiger_crmentity.smownerid, <br> vtiger_leaddetails.leadsource, <br> vtiger_crmentity.description, <br> vtiger_crmentity.createdtime, <br> vtiger_crmentity.modifiedtime, <br> vtiger_leaddetails.leadstatus, <br> vtiger_leaddetails.firstname, <br> vtiger_leaddetails.lastname, <br> vtiger_leadaddress.phone, <br> vtiger_leaddetails.email, <br> vtiger_leadscf.cf_999, <br> vtiger_leadscf.cf_927, <br> vtiger_leadscf.cf_1090, <br> vtiger_leadscf.cf_1110, <br> vtiger_leaddetails.leadid, <br> vtiger_crmentity_user_field.starred <br>FROM <br> vtiger_leaddetails <br> INNER JOIN vtiger_crmentity ON vtiger_leaddetails.leadid = vtiger_crmentity.crmid <br> LEFT JOIN vtiger_users ON vtiger_crmentity.smownerid = <a href="http://vtiger_users.id" target="_blank">vtiger_users.id</a> <br> LEFT JOIN vtiger_groups ON vtiger_crmentity.smownerid = vtiger_groups.groupid <br> INNER JOIN vtiger_leadaddress ON vtiger_leaddetails.leadid = vtiger_leadaddress.leadaddressid <br> INNER JOIN vtiger_leadscf ON vtiger_leaddetails.leadid = vtiger_leadscf.leadid <br> LEFT JOIN vtiger_crmentity_user_field ON vtiger_leaddetails.leadid = vtiger_crmentity_user_field.recordid <br> AND vtiger_crmentity_user_field.userid = 107 <br> INNER JOIN vt_tmp_u107_t7 vt_tmp_u107_t7 ON <a href="http://vt_tmp_u107_t7.id" target="_blank">vt_tmp_u107_t7.id</a> = vtiger_crmentity.smownerid <br>WHERE <br> vtiger_crmentity.deleted = 0 <br> and vtiger_leaddetails.converted = 0 <br> AND vtiger_leaddetails.leadid > 0 <br>ORDER BY <br> vtiger_crmentity.modifiedtime DESC <br>LIMIT <br> 0, 115;<br></div><div><br></div><div>So I have 2 questions:</div><div><br></div><div>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?</div><div>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.</div><div><br></div><div>I'll appreciate any help.</div><div><br clear="all"><div><div dir="ltr"><div dir="ltr">Saludos<div><br></div><div>Rubén</div></div></div></div></div></div>
_______________________________________________<br>
<a href="http://www.vtiger.com/" rel="noreferrer" target="_blank">http://www.vtiger.com/</a></blockquote></div>
<span>_______________________________________________</span><br><span>http://www.vtiger.com/</span></div></blockquote></div></body></html>