Can someone help me to optimizing this query?-CodePudding

I have the below query that takes so much time >> table size 64.31GB are there any rewrite possible for this?

SELECT MIN(ut.id) AS minUT, 
       MAX(ut.id) AS maxUT
FROM user_tasks ut
    JOIN user_tasks_metadata utm ON utm.user_task_id = ut.id
    JOIN ue_events_base ue ON ue.usr_task_id = ut.id
    JOIN batch_records br ON ut.id = br.user_task_id
WHERE ut.id > (
                SELECT IFNULL(MAX(assp.user_task_id_end) , 0) 
                FROM app_summary_snapshot_points assp
              )
AND br.created_at < (current_timestamp() - INTERVAL 10 MINUTE) 
AND br.is_subscription_updated = 1 
HAVING minUT > 0 and maxUT > 0;

the number of row scans

*************************** 1. row ***************************
           id: 1
  select_type: PRIMARY
        table: ue
   partitions: NULL
         type: range
possible_keys: ue_events_user_task_id_events
          key: ue_events_user_task_id_events
      key_len: 8
          ref: NULL
         rows: 1722
     filtered: 100.00
        Extra: Using where; Using index
*************************** 2. row ***************************
           id: 1
  select_type: PRIMARY
        table: br
   partitions: NULL
         type: ref
possible_keys: batch_created_at,batch_user_task
          key: batch_user_task
      key_len: 9
          ref: ue_stage.ue.usr_task_id
         rows: 1
     filtered: 5.00
        Extra: Using where
*************************** 3. row ***************************
           id: 1
  select_type: PRIMARY
        table: ut
   partitions: NULL
         type: eq_ref
possible_keys: PRIMARY
          key: PRIMARY
      key_len: 8
          ref: ue_stage.ue.usr_task_id
         rows: 1
     filtered: 100.00
        Extra: Using index
*************************** 4. row ***************************
           id: 1
  select_type: PRIMARY
        table: utm
   partitions: NULL
         type: ref
possible_keys: usertask_fk_idx,id_asi
          key: id_asi
      key_len: 8
          ref: ue_stage.ue.usr_task_id
         rows: 1
     filtered: 100.00
        Extra: Using index

the br table does not have some combo index but is there any rewrite possible for this query?

CodePudding user response：

Your WHERE filter for your batch_records table looks for

AND br.created_at < (current_timestamp() - INTERVAL 10 MINUTE) 
AND br.is_subscription_updated = 1

Therefore, a compound (multicolumn) covering index, like this, will help with performance.

ALTER TABLE batch_records ADD INDEX upd_cre_uti
        (is_subscription, created_at DESC, user_task_id);

MySQL can random-access that index to the first eligible row ... the one with the correct value of is_subscription and the largest created_at. It can then scan the index sequentially to the last eligible row. While it is scanning it can fetch user_task_id from the index.

I don't think there's a useful query refactoring that can fix your performance problem: indexes are the canonical way to address this kind of problem.