<div dir="ltr">Maybe using the "EXPLAIN" mysql function to see what indexes are being used, then trying to modify the query accordingly and/or add indexes to your database.<div><br></div><div>I'm no expert in this topic but at some point I had to optimize some queries in Vtiger (in my case it wasn't queries produced by vtiger, but rather own built queries). I found these two articles useful:</div><div><br></div><div>* <a href="https://www.sitepoint.com/using-explain-to-write-better-mysql-queries/">https://www.sitepoint.com/using-explain-to-write-better-mysql-queries/</a></div><div>* <a href="https://www.eversql.com/choosing-the-best-indexes-for-mysql-query-optimization/">https://www.eversql.com/choosing-the-best-indexes-for-mysql-query-optimization/</a></div><div><br></div><div>Hope that helps</div><div><br clear="all"><div><div dir="ltr" class="gmail_signature" data-smartmail="gmail_signature"><div dir="ltr">Saludos<div><br></div><div>Rubén</div></div></div></div><br></div></div><br><div class="gmail_quote"><div dir="ltr" class="gmail_attr">On Thu, Feb 11, 2021 at 2:04 PM Henry Cumbicus Rivera <<a href="mailto:hcumbicusr@gmail.com">hcumbicusr@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">Hello Ruben,<br>It is a report generated from the Vtiger 7.1 Reports module the query is built according to the selected fields or modules. How could I optimize the query?<br><div><br></div><div>This is a database of only 3GB.<br></div></div><br><div class="gmail_quote"><div dir="ltr" class="gmail_attr">El jue, 11 feb 2021 a las 14:55, Rubén A. Estrada Orozco (<<a href="mailto:rulotec1@gmail.com" target="_blank">rulotec1@gmail.com</a>>) escribió:<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">It would be great if queries were optimized. <div>Large databases start presenting those kind of issues at some point and it's not easy to debug.<div><br clear="all"><div><div dir="ltr"><div dir="ltr">Saludos<div><br></div><div>Rubén</div></div></div></div><br></div></div></div><br><div class="gmail_quote"><div dir="ltr" class="gmail_attr">On Thu, Feb 11, 2021 at 12:58 PM Henry Cumbicus Rivera <<a href="mailto:hcumbicusr@gmail.com" target="_blank">hcumbicusr@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">Hello,<br>I have a problem with report generation, specifically with the report record counter.<div><br></div><div><div>Report:</div><div>Primary module: Potentials</div><div>Secondary module: Contacts</div><div><br></div>These are the queries that are executed to count the records:<div><div><br></div><div><b><font color="#ff0000">SQL:</font></b></div><div>DROP TABLE vtiger_reptmptbl_16022b18c38f2c3425974780 ;<br>CREATE TEMPORARY TABLE vtiger_reptmptbl_16022b18c38f2c3425974780 AS SELECT<br> vtiger_contactdetails.*, cf_852,<br> cf_854,<br> cf_856,<br> cf_858,<br> cf_860,<br> cf_886,<br> cf_902,<br> cf_905,<br> cf_907,<br> cf_920,<br> cf_928,<br> cf_930<br>FROM<br> vtiger_contactdetails<br>INNER JOIN vtiger_crmentity ON vtiger_crmentity.crmid = vtiger_contactdetails.contactid<br>AND vtiger_crmentity.deleted = 0<br>LEFT JOIN vtiger_contactscf ON vtiger_contactscf.contactid = vtiger_contactdetails.contactid;<br><br>ALTER TABLE vtiger_reptmptbl_16022b18c38f2c3425974780 ADD INDEX (contactid);<br><br>-- ALTER TABLE vtiger_reptmptbl_16022b18c38f2c3425974780 ADD INDEX (potentialid); -- is commented because the field does not exist in the temporary table<br><br>SELECT<br> count(*) AS count<br>FROM<br> vtiger_potential<br>INNER JOIN vtiger_crmentity ON vtiger_crmentity.crmid = vtiger_potential.potentialid<br>INNER JOIN vtiger_potentialscf ON vtiger_potentialscf.potentialid = vtiger_potential.potentialid<br>LEFT JOIN (<br> SELECT<br> vtiger_contactdetails.*, cf_920<br> FROM<br> vtiger_contactdetails<br> INNER JOIN vtiger_crmentity ON vtiger_contactdetails.contactid = vtiger_crmentity.crmid<br> AND vtiger_crmentity.deleted = 0<br> LEFT JOIN vtiger_contactscf ON vtiger_contactdetails.contactid = vtiger_contactscf.contactid<br>) AS vtiger_contactdetailsPotentials ON vtiger_potential.contact_id = vtiger_contactdetailsPotentials.contactid<br>LEFT JOIN vtiger_groups vtiger_groupsPotentials ON vtiger_groupsPotentials.groupid = vtiger_crmentity.smownerid<br>LEFT JOIN vtiger_users AS vtiger_usersPotentials ON vtiger_usersPotentials.id = vtiger_crmentity.smownerid<br>LEFT JOIN vtiger_groups ON vtiger_groups.groupid = vtiger_crmentity.smownerid<br>LEFT JOIN vtiger_users ON <a href="http://vtiger_users.id" target="_blank">vtiger_users.id</a> = vtiger_crmentity.smownerid<br>LEFT JOIN vtiger_contpotentialrel ON vtiger_potential.potentialid = vtiger_contpotentialrel.potentialid<br><br><b><i><font color="#ff0000">-- Here is the problem, when doing JOIN using OR it takes more than 1 hour to return the result, if I remove the OR leaving any of the 2 conditions the result only takes 4 seconds.</font></i></b><br>LEFT JOIN vtiger_reptmptbl_16022b18c38f2c3425974780 AS vtiger_contactdetails <br>ON vtiger_potential.contact_id = vtiger_contactdetails.contactid<br>OR vtiger_contactdetails.contactid = vtiger_contpotentialrel.contactid<br><br>LEFT JOIN vtiger_crmentity AS vtiger_crmentityContacts ON vtiger_crmentityContacts.crmid = vtiger_contactdetails.contactid<br>AND vtiger_crmentityContacts.deleted = 0<br>LEFT JOIN vtiger_contactsubdetails ON vtiger_contactdetails.contactid = vtiger_contactsubdetails.contactsubscriptionid<br>LEFT JOIN vtiger_contactscf ON vtiger_contactdetails.contactid = vtiger_contactscf.contactid<br>WHERE<br> vtiger_potential.potentialid > 0<br>AND vtiger_crmentity.deleted = 0;<br></div><div><br></div><div><b>Another aspect is that the mysql processing is triggered by the pending query</b><br></div><div><br></div><div>NORMAL:</div><div><img src="cid:ii_kl17yuta0" alt="image.png" width="452" height="235"><br></div><div><br></div><div>With Query:</div><div><img src="cid:ii_kl17zunp1" alt="image.png" width="452" height="193"><br></div><div><br></div><div><br></div><div><br></div><div>Please help me if you have ever had to optimize tables, queries, reports or MySql itself to make it work properly.<br></div><div><br></div><div><br></div>-- <br><div dir="ltr"><div dir="ltr"><div><div dir="ltr"><div><div dir="ltr"><div><div dir="ltr"><div><div dir="ltr"><div><div dir="ltr"><br><div>---------------------------------------------------------------</div><div>Henry C.</div><div>Tel.: +51 956727976</div></div></div></div></div></div></div></div></div></div></div></div></div></div></div></div>
_______________________________________________<br>
<a href="http://www.vtiger.com/" rel="noreferrer" target="_blank">http://www.vtiger.com/</a></blockquote></div>
_______________________________________________<br>
<a href="http://www.vtiger.com/" rel="noreferrer" target="_blank">http://www.vtiger.com/</a></blockquote></div><br clear="all"><div><br></div>-- <br><div dir="ltr"><div dir="ltr"><div><div dir="ltr"><div><div dir="ltr"><div><div dir="ltr"><div><div dir="ltr"><div><div dir="ltr"><br><div>---------------------------------------------------------------</div><div>Henry C.</div><div>Tel.: 956727976</div></div></div></div></div></div></div></div></div></div></div></div></div>
_______________________________________________<br>
<a href="http://www.vtiger.com/" rel="noreferrer" target="_blank">http://www.vtiger.com/</a></blockquote></div>