MySQL - 我的 View 很慢

Question

我有 4 个表:

• 学生 超过 7000 条记录
• 教师 拥有超过 600 条记录
• 大学 超过30条记录
• Lessons_Meta 超过 10000 条记录

表学生列:

id | student_number | name

表教师列:

id | teacher_number | name

表大学列:

id | name

表 Lessons_Meta 列:

id | lesson_code | lesson_name | student_id | teacher_id | university_id

我的观点是:

CREATE VIEW `view_lessons` AS
SELECT
        *,
        (SELECT `name` FROM `students` WHERE `students`.`id` = `lessons_meta`.`student_id` LIMIT 1) AS "student_name",
        (SELECT `name` FROM `teachers` WHERE `teachers`.`id` = `lessons_meta`.`teacher_id` LIMIT 1) AS "teacher_name",
        (SELECT `name` FROM `universities` WHERE `universities`.`id` = `lessons_meta`.`university_id` LIMIT 1) AS "university_name"
FROM `lessons_meta`

我的查询是:

SELECT * FROM `view_lessons` LIMIT 20

20 秒后，显示查询完成!我怎样才能减少这个时间负荷？

Answer 1

您可能只需要索引。从以下索引开始:

CREATE INDEX idx_students_id_name ON students(id, name);
CREATE INDEX idx_teachers_id_name ON teachers(id, name);
CREATE INDEX idx_universities_id_name ON universities(id, name);