[Vtigercrm-developers] vtiger slowing down (a lot)

[Rubén A. Estrada Orozco]

Hi everyone,

In cases where a  vtiger database gets big over time, queries start to take
longer and longer. In some cases the database gets big because of junk
records. In those cases one might want to delete those records to improve
performance.
In a recent case I noticed that a query for loading the detail view of the
Leads module was taking aprox 10 seconds with ~409k records. Here is what I
got from the slow query log (Note that the rows being examined is ~3.6
million):

# Query_time: 10.803175  Lock_time: 0.000091  Rows_sent: 115
 Rows_examined: 3593722
# Rows_affected: 0  Bytes_sent: 19074
SET timestamp = 1664300474;
SELECT
  vtiger_crmentity.smownerid,
  vtiger_leaddetails.leadsource,
  vtiger_crmentity.description,
  vtiger_crmentity.createdtime,
  vtiger_crmentity.modifiedtime,
  vtiger_leaddetails.leadstatus,
  vtiger_leaddetails.firstname,
  vtiger_leaddetails.lastname,
  vtiger_leadaddress.phone,
  vtiger_leaddetails.email,
  vtiger_leadscf.cf_999,
  vtiger_leadscf.cf_927,
  vtiger_leadscf.cf_1090,
  vtiger_leadscf.cf_1110,
  vtiger_leaddetails.leadid,
  vtiger_crmentity_user_field.starred
FROM
  vtiger_leaddetails
  INNER JOIN vtiger_crmentity ON vtiger_leaddetails.leadid =
vtiger_crmentity.crmid
  LEFT JOIN vtiger_users ON vtiger_crmentity.smownerid = vtiger_users.id
  LEFT JOIN vtiger_groups ON vtiger_crmentity.smownerid =
vtiger_groups.groupid
  INNER JOIN vtiger_leadaddress ON vtiger_leaddetails.leadid =
vtiger_leadaddress.leadaddressid
  INNER JOIN vtiger_leadscf ON vtiger_leaddetails.leadid =
vtiger_leadscf.leadid
  LEFT JOIN vtiger_crmentity_user_field ON vtiger_leaddetails.leadid =
vtiger_crmentity_user_field.recordid
  AND vtiger_crmentity_user_field.userid = 107
  INNER JOIN vt_tmp_u107_t7 vt_tmp_u107_t7 ON vt_tmp_u107_t7.id =
vtiger_crmentity.smownerid
WHERE
  vtiger_crmentity.deleted = 0
  and vtiger_leaddetails.converted = 0
  AND vtiger_leaddetails.leadid > 0
ORDER BY
  vtiger_crmentity.modifiedtime DESC
LIMIT
  0, 115;

So I have 2 questions:

1) If I decide to delete records, is it enough to  just set
vtiger_crmentity.deleted = 1. Or should I entirely delete the records from
the database? If so, how?
2) The database I'm dealing with has plenty of RAM available and I don't
think that's an issue. So, is there a way to optimize the queries to get
better speeds? I can't get an "Explain" from the query because I get an
error saying that the table vt_tmp_u107_t7 does not exist.

I'll appreciate any help.

Saludos

Rubén
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.vtigercrm.com/pipermail/vtigercrm-developers/attachments/20220928/a7aa7dbf/attachment.html>