查询结果需要时间加载

0tdrvxhp 于 2021-06-25 发布在 Mysql

关注(0)|答案(2)|浏览(270)

我有一个从六个表中获取数据的查询，但是获取数据花费了太多的时间。浏览器会加载结果，有时什么也不显示。当我在mysql数据库中运行此查询时，执行此查询需要很长时间。

SELECT SQL_CALC_FOUND_ROWS movies.*,
curriculums.name AS curriculum,
teachers.name AS teacher,
movie_sub_categories.name AS sub_cat_name,
movie_categories.name AS cat_name
FROM movies
LEFT JOIN curriculums on movies.curriculum_id = curriculums.id
LEFT JOIN teachers on movies.teacher_id = teachers.id
LEFT JOIN movies_movie_sub_categories on movies.id = movies_movie_sub_categories.movie_id
LEFT JOIN movie_sub_categories on movies_movie_sub_categories.movie_sub_category_id = movie_sub_categories.id
LEFT JOIN movie_categories on movie_sub_categories.movie_category_id = movie_categories.id
ORDER BY id LIMIT 0, 50

这里是我所有的table结构

mysql performance left-join

来源：https://stackoverflow.com/questions/50238477/query-result-taking-time-to-load

2条答案

按热度按时间

2exbekwf1#

这不是一个非常令人兴奋的查询——它只是传递表的前50行 id 属于。什么时候 JOINing ，请限定列，以便我们知道发生了什么。
你真的需要吗 LEFT ?
假设你需要 LEFT 以及 id 属于 movies ，则运行速度会更快：
同时，找出每行有多少行 movies 只有一次，所以你不必每次都计算。

SELECT  movies.*, curriculums.name AS curriculum,
        teachers.name AS teacher, movie_sub_categories.name AS sub_cat_name,
        movie_categories.name AS cat_name
    FROM  ( SELECT id FROM movies ORDER BY id LIMIT 0, 50 ) AS m
    JOIN  movies USING(id)
    LEFT JOIN  curriculums AS c  ON movies.curriculum_id = c.id
    LEFT JOIN  teachers AS t  ON movies.teacher_id = t.id
    LEFT JOIN  movies_movie_sub_categories AS mmsc  ON movies.id = mmsc.movie_id
    LEFT JOIN  movie_sub_categories AS msc  ON mmsc.movie_sub_category_id = msc.id
    LEFT JOIN  movie_categories AS mc  ON msc.movie_category_id = mc.id
    ORDER BY  m.id

请使用 SHOW CREATE TABLE ; 我们需要看看你是否有足够的索引，比如