查询需要很长时间才能执行 [英] Query takes a very long time to execute

问题描述

我目前正在尝试执行一个查询，但是让它加载了几分钟后，我认为可能是我做错了.

I have a query that I am trying to execute at the moment but after letting it load for a few minutes I am thinking there is probably something I am doing wrong.

这是我要执行的操作:

SELECT *
FROM open_hours
INNER JOIN open_times
    ON open_hours.id = open_times.open_hour_id
INNER JOIN off_peak_times
    ON open_hours.id = off_peak_times.open_hour_id;

我有三个表:

开放时间:

大约14000行

id;
owner_type;
owner_id;
seats;
time_interval;
max_at_one_time;
created_at;
updated_at;
minutes_in_advance;

开放时间:

大约18,000行

id;
open_hour_id;
time_start;
time_end;
weekday;
created_at;
meal_type;

非高峰时间:

大约1万行

id;
open_hour_id;
time;
discount;
seats;
created_at;
updated_at;
weekday;

我已经尝试将SELECT *减少为SELECT open_hours.id，open_times.meal_type，因为实际上这是我现在想要获取的唯一数字.它使我在大约30秒内获得了结果，这对我来说仍然听起来很像.

I have tried reducing the SELECT * to a SELECT open_hours.id, open_times.meal_type since in reality that's the only number I'm trying to get right now. It gets me the results in roughly 30 seconds, which still sounds like a lot to me.

我在更大的其他表上运行其他查询，但几乎立即获得了结果，所以我缺少或不考虑某些东西吗?

I run other queries on other tables that are a lot bigger and yet get results almost instantly, so is there something that I am missing or not considering?

推荐答案

要使查询更快，您可以执行三件事. 不知道它是否会帮助您，但根据SQL优化查询概念，它一定会帮助您.

To make the query faster in your case there have three things you can do. Don't know it will help you or not but According to SQL optimizing query concepts, it must help.

1. 为所有表创建CLUSTERED INDEX.簇索引使SELECT查询以30:70的比率更快.
2. 您应创建列列表，而不要使用STAR语句(避免使用SELECT *).额外的列会使BUFFER-POOL变重.
3. 您可以使用VIEW代替查询.因为JOIN语句期间的VIEW比普通查询要好.

1. Create CLUSTERED INDEX for all your tables. Cluster index makes the SELECT query faster at 30:70 of ratio.
2. You should make column list instead of STAR statement (avoid SELECT *). Extra columns make BUFFER-POOL heavy.
3. You can use VIEW instead of the query. Because VIEW during JOIN statements is better than the normal query.

必须在三个选项中都优先使用第一个选项(CLUSTER INDEX)，这将真正提高性能. 希望这会有所帮助.

From above all of three, you must try first option (CLUSTER INDEX), which will really improve performance. Hope this will be helpful.

这篇关于查询需要很长时间才能执行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！