<div dir="ltr"><div dir="ltr">Adding the modifiedtime index didn't make a difference.<br></div><div>I have this query that is apparently being used to load the listview of the Invoice module:</div><div><br></div><div># Query_time: 10.208737  Lock_time: 0.000086  Rows_sent: 101  Rows_examined: 6813329<br># Rows_affected: 0  Bytes_sent: 11919<br>SELECT <br>  vtiger_invoice.subject, <br>  vtiger_invoice.salesorderid, <br>  vtiger_invoice.invoicestatus, <br>  vtiger_invoice.total, <br>  vtiger_crmentity.smownerid, <br>  vtiger_invoice.invoicedate, <br>  vtiger_invoice.duedate, <br>  vtiger_invoicecf.cf_1498, <br>  vtiger_invoice.invoice_no, <br>  vtiger_invoicecf.spl_payment_id, <br>  vtiger_invoicecf.spl_uuid, <br>  vtiger_invoice.invoiceid, <br>  vtiger_crmentity_user_field.starred <br>FROM <br>  vtiger_invoice <br>  INNER JOIN vtiger_crmentity ON vtiger_invoice.invoiceid = vtiger_crmentity.crmid <br>  LEFT JOIN vtiger_users ON vtiger_crmentity.smownerid = <a href="http://vtiger_users.id">vtiger_users.id</a> <br>  LEFT JOIN vtiger_groups ON vtiger_crmentity.smownerid = vtiger_groups.groupid <br>  INNER JOIN vtiger_invoicecf ON vtiger_invoice.invoiceid = vtiger_invoicecf.invoiceid <br>  LEFT JOIN vtiger_crmentity_user_field ON vtiger_invoice.invoiceid = vtiger_crmentity_user_field.recordid <br>  AND vtiger_crmentity_user_field.userid = 1 <br>WHERE <br>  vtiger_crmentity.deleted = 0 <br>  AND vtiger_invoice.invoiceid > 0 <br>ORDER BY <br>  vtiger_crmentity.modifiedtime DESC <br>LIMIT <br>  0, 101;<br></div><div dir="ltr"><br></div><div>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.</div><div><br></div><div>This is the EXPLAIN output (where I can't really see any obvious improvement opportunities):</div><div><br></div><div>*************************** 1. row ***************************<br>           id: 1<br>  select_type: SIMPLE<br>        table: vtiger_invoice<br>         type: range<br>possible_keys: PRIMARY,invoice_purchaseorderid_idx<br>          key: PRIMARY<br>      key_len: 4<br>          ref: NULL<br>         rows: 675575<br>        Extra: Using where; Using temporary; Using filesort<br>*************************** 2. row ***************************<br>           id: 1<br>  select_type: SIMPLE<br>        table: vtiger_invoicecf<br>         type: eq_ref<br>possible_keys: PRIMARY<br>          key: PRIMARY<br>      key_len: 4<br>          ref: vt_sandbox.vtiger_invoice.invoiceid<br>         rows: 1<br>        Extra: <br>*************************** 3. row ***************************<br>           id: 1<br>  select_type: SIMPLE<br>        table: vtiger_crmentity<br>         type: eq_ref<br>possible_keys: PRIMARY,crmentity_deleted_idx<br>          key: PRIMARY<br>      key_len: 4<br>          ref: vt_sandbox.vtiger_invoice.invoiceid<br>         rows: 1<br>        Extra: Using where<br>*************************** 4. row ***************************<br>           id: 1<br>  select_type: SIMPLE<br>        table: vtiger_crmentity_user_field<br>         type: eq_ref<br>possible_keys: record_user_idx<br>          key: record_user_idx<br>      key_len: 10<br>          ref: vt_sandbox.vtiger_invoice.invoiceid,const<br>         rows: 1<br>        Extra: <br></div><div dir="ltr"><br clear="all"><div><div dir="ltr" class="gmail_signature" data-smartmail="gmail_signature"><div>Any thoughts? </div><div><br></div><div>Right now the only solution I see is to avoid the "All" lists as also suggested by Tom.</div><div dir="ltr"><br></div><div dir="ltr">Saludos<div><br></div><div>Rubén</div></div></div></div></div></div>