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

Slow query – how to improve query performance in AZure mysql

I have a three table. When I execute below query it takes 3 to 5 min.

SELECT u.Id,cmc.completionstate,cmc.coursemoduleid
FROM User u
JOIN role_assignments ra ON  u.id =ra.userid
LEFT JOIN course_modules_completion  cmc ON u.id=cmc.userid  
LIMIT 1000000000

Execution Plan:

id  select_type  table   type    possible_keys             key                       key_len  ref                             rows    Extra        

 1  SIMPLE       u       index   PRIMARY                   mdl_user_del_ix           1        (NULL)                          31018   Using index  
 1  SIMPLE       ra      ref     mdl_roleassi_use_ix       mdl_roleassi_use_ix       8        tpsmoodle_prod_moodle_new.u.id  2       Using index  
 1  SIMPLE       cmc     ref     mdl_courmoducomp_use_key  mdl_courmoducomp_use_key  8        tpsmoodle_prod_moodle_new.u.id  41
Tags:

2

Answers


  1. 0

    There are multiple ways how to improve the speed

    • Scaling: Vertical / Horizontal
    • Partitioning / Indexing: Improve "internal" structure
    • Query: Selecting only required data SELECT TOP(10)...
    Login or Signup to reply.
  2. 0

    This seems to be unused (except to validate that there is a row in ra). Remove it:

    JOIN  role_assignments ra  ON u.id =ra.userid

    Much of the slowness is due to reaching into ra. If you really need it, we can discuss whether an EXISTS would be an appropriate improvement.

    Another point: (This derives from the "2" in EXPLAIN.) You are probably getting multiple identical output rows simply because there are many ra rows matching ra.userid.

    The LIMIT probably had no effect; consider removing it.

    Please provide SHOW CREATE TABLE for each table. In particular, there does not seem to be any index starting with userid in cmc. So, I recommend either of these:

    INDEX(userid)
INDEX(userid,  completionstate, coursemoduleid)
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search