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

[Rubén A. Estrada Orozco]

Adding the modifiedtime index didn't make a difference.
I have this query that is apparently being used to load the listview of the
Invoice module:

# Query_time: 10.208737  Lock_time: 0.000086  Rows_sent: 101
 Rows_examined: 6813329
# Rows_affected: 0  Bytes_sent: 11919
SELECT
  vtiger_invoice.subject,
  vtiger_invoice.salesorderid,
  vtiger_invoice.invoicestatus,
  vtiger_invoice.total,
  vtiger_crmentity.smownerid,
  vtiger_invoice.invoicedate,
  vtiger_invoice.duedate,
  vtiger_invoicecf.cf_1498,
  vtiger_invoice.invoice_no,
  vtiger_invoicecf.spl_payment_id,
  vtiger_invoicecf.spl_uuid,
  vtiger_invoice.invoiceid,
  vtiger_crmentity_user_field.starred
FROM
  vtiger_invoice
  INNER JOIN vtiger_crmentity ON vtiger_invoice.invoiceid =
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_invoicecf ON vtiger_invoice.invoiceid =
vtiger_invoicecf.invoiceid
  LEFT JOIN vtiger_crmentity_user_field ON vtiger_invoice.invoiceid =
vtiger_crmentity_user_field.recordid
  AND vtiger_crmentity_user_field.userid = 1
WHERE
  vtiger_crmentity.deleted = 0
  AND vtiger_invoice.invoiceid > 0
ORDER BY
  vtiger_crmentity.modifiedtime DESC
LIMIT
  0, 101;

As you can see it took ~10 seconds. Just removing the ORDER BY clause makes
it take 0.001 seconds. So, as suggested by Tom, the ORDER BY is causing the
slowness.

This is the EXPLAIN output (where I can't really see any obvious
improvement opportunities):

*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: vtiger_invoice
         type: range
possible_keys: PRIMARY,invoice_purchaseorderid_idx
          key: PRIMARY
      key_len: 4
          ref: NULL
         rows: 675575
        Extra: Using where; Using temporary; Using filesort
*************************** 2. row ***************************
           id: 1
  select_type: SIMPLE
        table: vtiger_invoicecf
         type: eq_ref
possible_keys: PRIMARY
          key: PRIMARY
      key_len: 4
          ref: vt_sandbox.vtiger_invoice.invoiceid
         rows: 1
        Extra:
*************************** 3. row ***************************
           id: 1
  select_type: SIMPLE
        table: vtiger_crmentity
         type: eq_ref
possible_keys: PRIMARY,crmentity_deleted_idx
          key: PRIMARY
      key_len: 4
          ref: vt_sandbox.vtiger_invoice.invoiceid
         rows: 1
        Extra: Using where
*************************** 4. row ***************************
           id: 1
  select_type: SIMPLE
        table: vtiger_crmentity_user_field
         type: eq_ref
possible_keys: record_user_idx
          key: record_user_idx
      key_len: 10
          ref: vt_sandbox.vtiger_invoice.invoiceid,const
         rows: 1
        Extra:

Any thoughts?

Right now the only solution I see is to avoid the "All" lists as also
suggested by Tom.

Saludos

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