有沒有辦法優化這個mysql查詢
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
這個查詢需要 0.14 秒或更長時間來執行，我想優化時間。
解釋相同的查詢
索引詳情
d 表
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表
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 表
PRIMARY KEY (`course_id`),
UNIQUE KEY `path` (`path`),
KEY `program` (`program`,`stream`),
KEY `stream` (`stream`,`course_id`),
一張桌子
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`)
uj5u.com熱心網友回復：
由于您只從 中進行選擇cm_institute，因此另一種方法是使用EXISTS有時比連接性能更好的方法。
也不需要DISTINCTor 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
);
uj5u.com熱心網友回復：
對于多對多映射，索引可能是性能問題。
您只需要這些索引：
# 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`),
更多討論：http : //mysql.rjweb.org/doc.php/index_cookbook_mysql#many_to_many_mapping_table
此外，這些索引：
a: INDEX(path, stream_id)
b: INDEX(stream, course_id)
d: INDEX(institute_id) -- unless that is the PK
如需進一步討論，請提供SHOW CREATE TABLE每張表。
轉載請註明出處，本文鏈接：https://www.uj5u.com/shujuku/353724.html