在这种情况下如何避免嵌套的SQL查询? [英] How to avoid nested SQL query in this case?

问题描述

我有一个与此和

I have an SQL question, related to this and this question (but different). Basically I want to know how I can avoid a nested query.

比方说，我有一家公司在其历史上执行过的大量工作(jobs).这些作业的特点是年份，月份，位置以及该作业所用工具的代码.另外，我还有一个工具表(tools)，可将工具代码转换为工具描述以及有关该工具的更多数据.现在他们想要一个网站，可以在其中使用下拉框选择年，月，位置和工具，然后将显示匹配的作业.我只想用与之前选择的年份，月份和位置相匹配的相关工具来填充最后一个下拉列表，所以我编写了以下嵌套查询:

Let's say I have a huge table of jobs (jobs) executed by a company in their history. These jobs are characterized by year, month, location and the code belonging to the tool used for the job. Additionally I have a table of tools (tools), translating tool codes to tool descriptions and further data about the tool. Now they want a website where they can select year, month, location and tool using a dropdown box, after which the matching jobs will be displayed. I want to fill the last dropdown with only the relevant tools matching the before selection of year, month and location, so I write the following nested query:

SELECT c.tool_code, t.tool_description
FROM (
 SELECT DISTINCT j.tool_code
 FROM jobs AS j
 WHERE j.year = ....
        AND j.month = ....
 AND j.location = ....
) AS c
LEFT JOIN tools as t
ON c.tool_code = t.tool_code
ORDER BY c.tool_code ASC

我求助于此嵌套查询，因为它比在整个数据库上执行JOIN并从中进行选择要快得多.它使我的查询时间大大减少了.但是正如我最近读到的那样，

I resorted to this nested query because it was much faster than performing a JOIN on the complete database and selecting from that. It got my query time down a lot. But as I have recently read that MySQL nested queries should be avoided at all cost, I am wondering whether I am wrong in this approach. Should I rewrite my query differently? And how?

推荐答案

不，您不应该，您的查询很好.

No, you shouldn't, your query is fine.

只需在jobs (year, month, location, tool_code)和tools (tool_code)上创建索引，以便可以使用INDEX FOR GROUP-BY.

Just create an index on jobs (year, month, location, tool_code) and tools (tool_code) so that the INDEX FOR GROUP-BY can be used.

您提供的文章描述了子查询谓词(IN (SELECT ...))，而不是嵌套查询(SELECT FROM (SELECT ...)).

The article your provided describes the subquery predicates (IN (SELECT ...)), not the nested queries (SELECT FROM (SELECT ...)).

即使有子查询，该文章也是错误的:虽然MySQL不能优化所有子查询，但它可以很好地处理IN (SELECT …)谓词.

Even with the subqueries, the article is wrong: while MySQL is not able to optimize all subqueries, it deals with IN (SELECT …) predicates just fine.

我不知道为什么作者选择将DISTINCT放在这里:

I don't know why the author chose to put DISTINCT here:

SELECT  id, name, price
FROM    widgets
WHERE   id IN
        (
        SELECT  DISTINCT widgetId
        FROM    widgetOrders
        )

以及为什么他们认为这将有助于提高性能，但是考虑到widgetID已建立索引，MySQL只会转换此查询:

and why do they think this will help to improve performance, but given that widgetID is indexed, MySQL will just transform this query:

SELECT  id, name, price
FROM    widgets
WHERE   id IN
        (
        SELECT  widgetId
        FROM    widgetOrders
        )

放入index_subquery

从本质上讲，这就像EXISTS子句:内部子查询将对每个widgets行执行一次，并添加其他谓词:

Essentially, this is just like EXISTS clause: the inner subquery will be executed once per widgets row with the additional predicate added:

SELECT  NULL
FROM    widgetOrders
WHERE   widgetId = widgets.id

并在widgetOrders的第一场比赛中停下.

and stop on the first match in widgetOrders.

此查询:

SELECT  DISTINCT w.id,w.name,w.price
FROM    widgets w
INNER JOIN
        widgetOrders o
ON      w.id = o.widgetId

将不得不使用temporary来消除重复项，并且速度会慢得多.

will have to use temporary to get rid of the duplicates and will be much slower.

这篇关于在这种情况下如何避免嵌套的SQL查询?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！