Question 113562 in Mysql Question posted in
The official MySQL documentation can be found here.

Mysql – Can someone help me to optimizing this query?

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?

Tags:

2

Answers


  1. 0

    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, 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.

    Login or Signup to reply.
  2. 0 
    br:  (is_subscription_updated, created_at, user_task_id)
assp:  (user_task_id_end)
utm:  INDEX(user_task_id)
ue:  INDEX(usr_task_id)

    Do you really need utm and ue in the list of Joins?

    What is the kludge with MIN, MAX and IFNULL? (There may be a better way to achieve your goal.)

    Please provide EXPLAIN SELECT ... after making the changes.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search