时间戳

首页 > 解决方案 > 查询结果需要时间加载

mysql - 查询结果需要时间加载

问题描述

我有一个从六个表中获取数据的查询，但是获取数据需要花费太多时间。浏览器加载并且有时没有显示任何结果。当我在 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

这是我所有的表结构 在此处输入图像描述 在此处输入图像描述 在此处输入图像描述 在此处输入图像描述 在此处输入图像描述 在此处输入图像描述

标签： mysqlperformanceleft-join

解决方案

id这不是一个非常令人兴奋的查询——它只是提供属于哪个表的前 50 行。何时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; 我们需要看看你是否有足够的索引，比如

mmsc:  INDEX(movie_id)

推荐阅读