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

[Angelo Paglialonga]

Hi Ruben I still suggest you to apply indexes to the all  fields in select query and check the results if you are curious

_______________

Angelo Paglialonga
Consulente per soluzioni CRM ad alto ROI.

Telefono: 3386077866
Web: https://www.angelopaglialonga.com


> Il giorno 30 set 2022, alle ore 21:51, Rubén A. Estrada Orozco <rulotec1 at gmail.com> ha scritto:
> 
> 
> 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
> _______________________________________________
> http://www.vtiger.com/
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.vtigercrm.com/pipermail/vtigercrm-developers/attachments/20220930/fff6a423/attachment.html>