[Vtigercrm-developers] Report records count error

[Rubén A. Estrada Orozco]

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/
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.vtigercrm.com/pipermail/vtigercrm-developers/attachments/20210211/0ecb7a30/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/0ecb7a30/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/0ecb7a30/attachment-0003.png>