[Vtigercrm-developers] Report records count error

[Henry Cumbicus Rivera]

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