[Vtigercrm-developers] Report records count error

Rubén A. Estrada Orozco rulotec1 at gmail.com
Thu Feb 11 22:54:01 GMT 2021


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.

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:

* https://www.sitepoint.com/using-explain-to-write-better-mysql-queries/
*
https://www.eversql.com/choosing-the-best-indexes-for-mysql-query-optimization/

Hope that helps

Saludos

Rubén


On Thu, Feb 11, 2021 at 2:04 PM Henry Cumbicus Rivera <hcumbicusr at gmail.com>
wrote:

> Hello Ruben,
> 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?
>
> This is a database of only 3GB.
>
> El jue, 11 feb 2021 a las 14:55, Rubén A. Estrada Orozco (<
> rulotec1 at gmail.com>) escribió:
>
>> It would be great if queries were optimized.
>> Large databases start presenting those kind of issues at some point and
>> it's not easy to debug.
>>
>> Saludos
>>
>> Rubén
>>
>>
>> On Thu, Feb 11, 2021 at 12:58 PM Henry Cumbicus Rivera <
>> hcumbicusr at gmail.com> wrote:
>>
>>> Hello,
>>> I have a problem with report generation, specifically with the report
>>> record counter.
>>>
>>> Report:
>>> Primary module: Potentials
>>> Secondary module: Contacts
>>>
>>> These are the queries that are executed to count the records:
>>>
>>> *SQL:*
>>> DROP TABLE vtiger_reptmptbl_16022b18c38f2c3425974780  ;
>>> CREATE TEMPORARY TABLE vtiger_reptmptbl_16022b18c38f2c3425974780 AS
>>> SELECT
>>> vtiger_contactdetails.*, cf_852,
>>> cf_854,
>>> cf_856,
>>> cf_858,
>>> cf_860,
>>> cf_886,
>>> cf_902,
>>> cf_905,
>>> cf_907,
>>> cf_920,
>>> cf_928,
>>> cf_930
>>> FROM
>>> vtiger_contactdetails
>>> INNER JOIN vtiger_crmentity ON vtiger_crmentity.crmid =
>>> vtiger_contactdetails.contactid
>>> AND vtiger_crmentity.deleted = 0
>>> LEFT JOIN vtiger_contactscf ON vtiger_contactscf.contactid =
>>> vtiger_contactdetails.contactid;
>>>
>>> ALTER TABLE vtiger_reptmptbl_16022b18c38f2c3425974780 ADD INDEX
>>> (contactid);
>>>
>>> -- ALTER TABLE vtiger_reptmptbl_16022b18c38f2c3425974780 ADD INDEX
>>> (potentialid); -- is commented because the field does not exist in the
>>> temporary table
>>>
>>> SELECT
>>> count(*) AS count
>>> FROM
>>> vtiger_potential
>>> INNER JOIN vtiger_crmentity ON vtiger_crmentity.crmid =
>>> vtiger_potential.potentialid
>>> INNER JOIN vtiger_potentialscf ON vtiger_potentialscf.potentialid =
>>> vtiger_potential.potentialid
>>> LEFT JOIN (
>>> SELECT
>>> vtiger_contactdetails.*, cf_920
>>> FROM
>>> vtiger_contactdetails
>>> INNER JOIN vtiger_crmentity ON vtiger_contactdetails.contactid =
>>> vtiger_crmentity.crmid
>>> AND vtiger_crmentity.deleted = 0
>>> LEFT JOIN vtiger_contactscf ON vtiger_contactdetails.contactid =
>>> vtiger_contactscf.contactid
>>> ) AS vtiger_contactdetailsPotentials ON vtiger_potential.contact_id =
>>> vtiger_contactdetailsPotentials.contactid
>>> LEFT JOIN vtiger_groups vtiger_groupsPotentials ON
>>> vtiger_groupsPotentials.groupid = vtiger_crmentity.smownerid
>>> LEFT JOIN vtiger_users AS vtiger_usersPotentials ON
>>> vtiger_usersPotentials.id = vtiger_crmentity.smownerid
>>> LEFT JOIN vtiger_groups ON vtiger_groups.groupid =
>>> vtiger_crmentity.smownerid
>>> LEFT JOIN vtiger_users ON vtiger_users.id = vtiger_crmentity.smownerid
>>> LEFT JOIN vtiger_contpotentialrel ON vtiger_potential.potentialid =
>>> vtiger_contpotentialrel.potentialid
>>>
>>> *-- 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.*
>>> LEFT JOIN vtiger_reptmptbl_16022b18c38f2c3425974780 AS
>>> vtiger_contactdetails
>>> ON vtiger_potential.contact_id = vtiger_contactdetails.contactid
>>> OR vtiger_contactdetails.contactid = vtiger_contpotentialrel.contactid
>>>
>>> LEFT JOIN vtiger_crmentity AS vtiger_crmentityContacts ON
>>> vtiger_crmentityContacts.crmid = vtiger_contactdetails.contactid
>>> AND vtiger_crmentityContacts.deleted = 0
>>> LEFT JOIN vtiger_contactsubdetails ON vtiger_contactdetails.contactid =
>>> vtiger_contactsubdetails.contactsubscriptionid
>>> LEFT JOIN vtiger_contactscf ON vtiger_contactdetails.contactid =
>>> vtiger_contactscf.contactid
>>> WHERE
>>> vtiger_potential.potentialid > 0
>>> AND vtiger_crmentity.deleted = 0;
>>>
>>> *Another aspect is that the mysql processing is triggered by the pending
>>> query*
>>>
>>> NORMAL:
>>> [image: image.png]
>>>
>>> With Query:
>>> [image: image.png]
>>>
>>>
>>>
>>> Please help me if you have ever had to optimize tables, queries, reports
>>> or MySql itself to make it work properly.
>>>
>>>
>>> --
>>>
>>> ---------------------------------------------------------------
>>> Henry C.
>>> Tel.: +51 956727976
>>> _______________________________________________
>>> http://www.vtiger.com/
>>
>> _______________________________________________
>> http://www.vtiger.com/
>
>
>
> --
>
> ---------------------------------------------------------------
> Henry C.
> Tel.: 956727976
> _______________________________________________
> http://www.vtiger.com/
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.vtigercrm.com/pipermail/vtigercrm-developers/attachments/20210211/be5b398a/attachment-0001.html>
-------------- next part --------------
A non-text attachment was scrubbed...
Name: image.png
Type: image/png
Size: 59544 bytes
Desc: not available
URL: <http://lists.vtigercrm.com/pipermail/vtigercrm-developers/attachments/20210211/be5b398a/attachment-0002.png>
-------------- next part --------------
A non-text attachment was scrubbed...
Name: image.png
Type: image/png
Size: 48328 bytes
Desc: not available
URL: <http://lists.vtigercrm.com/pipermail/vtigercrm-developers/attachments/20210211/be5b398a/attachment-0003.png>


More information about the vtigercrm-developers mailing list