Mysql query optimization on multiple table join-CodePudding

Is there a way to optimize this mysql query

SELECT d.* 
FROM cm_stream a 
INNER JOIN cm_courses b ON a.stream_id = b.stream 
INNER JOIN cm_institute_courses c ON b.course_id = c.course 
INNER JOIN cm_institute d ON d.institute_id = c.institute 
WHERE a.path = 'engineering' 
GROUP BY c.institute

This query taking 0.14s or more to execute and i want to optimize the time.

EXPLAIN FOR SAME QUERY

CodePudding user response：

Since you are selecting only from cm_institute, an alternative way would be with EXISTS which sometimes performs better than joins.
Also there is no need for DISTINCT or GROUP BY:

SELECT d.* 
FROM cm_institute d
WHERE EXISTS (
  SELECT 1
  FROM cm_stream a 
  INNER JOIN cm_courses b ON a.stream_id = b.stream 
  INNER JOIN cm_institute_courses c ON b.course_id = c.course 
  WHERE a.path = 'engineering' AND d.institute_id = c.institute
);

CodePudding user response：

For many-to-many mappings, the indexes are probably the performance problem.

You need only these indexes:

 PRIMARY KEY(course, institute),
 INDEX(institute, course)

More discussion: http://mysql.rjweb.org/doc.php/index_cookbook_mysql#many_to_many_mapping_table

Also, these indexes:

a:  INDEX(path, stream_id)
b:  INDEX(stream,  course_id)
d:  INDEX(institute_id)  -- unless that is the PK

For further discussion, please provide SHOW CREATE TABLE for each table.