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

[Tom Brown]

Angelo,

The index is definitely the #1 thing Ruben should try, however in your
case, did it work for 400k plus records? (assuming default listview sort &
no filter)

Ruben,

vt_tmp_*** tables are temporary tables generated due to sharing access, you
can find the function getNonAdminAccessControlQuery in CRMEntity.php.

Can you also provide MySQL cluster specs (cpu/ram/version), as well as
provide the output from the query below.

SHOW GLOBAL VARIABLES
WHERE variable_name IN (
'sort_buffer_size',
'join_buffer_size',
'query_cache_size',
'tmp_table_size',
'innodb_buffer_pool_size',
'max_heap_table_size',
'open_files_limit',
'group_concat_max_len',
'query_cache_limit');

Thanks


---

*Tom Brown* / Development Manager
+1 (818) 495-5557 / tbrown at vtexperts.com

*VTExperts*
vtexperts.com

The information transmitted, including attachments, is intended only for
the person(s) or entity to which it is addressed and may contain
confidential and/or privileged material. Any review, retransmission,
dissemination or other use of, or taking of any action in reliance upon
this information by persons or entities other than the intended recipient
is prohibited. If you received this in error, please contact the sender and
destroy any copies of this information.


On Fri, Sep 30, 2022 at 3:11 PM Angelo Paglialonga <
info at angelopaglialonga.com> wrote:

> 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/
>
> _______________________________________________
> http://www.vtiger.com/
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.vtigercrm.com/pipermail/vtigercrm-developers/attachments/20221003/47e53963/attachment.html>