[Vtigercrm-developers] Report records count error
Henry Cumbicus Rivera
hcumbicusr at gmail.com
Thu Feb 11 20:01:25 GMT 2021
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
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.vtigercrm.com/pipermail/vtigercrm-developers/attachments/20210211/dc6c333f/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/dc6c333f/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/dc6c333f/attachment-0003.png>
More information about the vtigercrm-developers
mailing list