CODEWITHSUNDEEP
mysqljoininner-join
Hitesh
Hitesh

Reputation: 152

Mysql query optimization on multiple table join

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

enter image description here

INDEX DETAILS

d Table

  PRIMARY KEY (`institute_id`),
  UNIQUE KEY `cm_institute_unique` (`path`),
  KEY `importance` (`importance`),
  KEY `cover` (`cover`),
  KEY `logo` (`logo`),
  KEY `logo_2` (`logo`,`cover`,`importance`),
  KEY `active` (`active`),
  KEY `name` (`name`),
  KEY `abbreviation` (`abbreviation`),
  KEY `name_2` (`name`,`abbreviation`),
  KEY `path` (`path`,`institute_id`)

c Table

  PRIMARY KEY (`institute_course_id`),
  KEY `course_key` (`course`),
  KEY `active` (`active`),
  KEY `institute` (`institute`,`course`),
  KEY `cm_institute_cours_idx_institute_active` (`institute`,`active`),
  KEY `institute_2` (`institute`,`course`)

b Table

  PRIMARY KEY (`course_id`),
  UNIQUE KEY `path` (`path`),
  KEY `program` (`program`,`stream`),
  KEY `stream` (`stream`,`course_id`),

a Table

  PRIMARY KEY (`stream_id`),
  UNIQUE KEY `path` (`path`),
  KEY `active` (`active`),
  KEY `cm_stream_idx_name_stream_id` (`name`,`stream_id`),
  KEY `active_2` (`active`,`name`),
  KEY `path_2` (`path`,`stream_id`)

Upvotes: 1

Views: 614

Answers (2)

Rick James
Rick James

Reputation: 142298

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

You need only these indexes:

 # Basic for many_to_many table:
 PRIMARY KEY(course, institute),
 INDEX(institute, course)
 # Extras for your application:
  KEY `active` (`active`),
  KEY `cm_institute_cours_idx_institute_active` (`institute`,`active`),

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.

Upvotes: 0

forpas
forpas

Reputation: 164089

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
);

Upvotes: 1

Related Questions