I currently got this query:
我最近收到了这个查询:
SELECT
location.street,
location.zip,
location.city,
surface,
price_buy,
price_rental
FROM buildings
JOIN location
ON building.location_id = location.location_id
WHERE surface > 1000
The desired behaviour (in terms of performance) would be:
期望的行为(就表现而言)是:
- Filter out all results from
buildingswithsurface > 1000 - 过滤掉表面> 1000的所有结果
- Retrieve the
locationdata corresponding to the leftoverbuildingsentries. - 检索与剩余建筑物项对应的位置数据。
Is my query currently doing that? Syntactically I would expect that the WHERE would need to be before the JOIN, but is this even possible? Does MySQL itself does any optimisation, if so, does that same optimisation hold for all SQL languages?
我的查询当前是否正在这样做?从语法上来说，我认为在连接之前需要到达哪里，但这真的可能吗?MySQL本身是否进行了任何优化，如果是这样，是否对所有SQL语言都保持相同的优化?
2 个解决方案
#1
6
Firstly, your query is doing the opposite of what you want - it is filtering out (i.e. removing) buildings whose surface is <= 1000.
首先，您的查询做的与您想要的相反——它过滤(即删除)表面为<= 1000的建筑物。
Syntactically I would expect that the WHERE would need to be before the JOIN, but is this even possible?
从语法上来说，我认为在连接之前需要到达哪里，但这真的可能吗?
No, your syntax is fine. The reason the joins come first is because they identify the tables to query and there relationships; only after you've done that do you specify which rows you do and do not want.
不，你的语法没问题。join之所以排在前面是因为它们标识要查询的表和它们之间的关系;只有在这样做之后，才能指定要做和不想做的行。
The formality of SQL syntax allows the query optimizer to understand what you meant (as long as you
SQL语法的形式允许查询优化器理解您的意思(只要您
Does MySQL itself does any optimisation, if so, does that same optimisation hold for all SQL languages?
MySQL本身是否进行了任何优化，如果是这样，是否对所有SQL语言都保持相同的优化?
Yes, MySQL (and pretty much all SQL database engines) include a query optimizer, which translates your SQL into specific instructions. Notionally, SQL is a declarative language - you tell the computer what you want to achieve, and it is supposed to work out the most efficient way to do it - this is in contrast with imperative programming (PHP, C, Ruby etc.) where you tell the computer explicitly what to do, and in which order.
是的，MySQL(以及几乎所有的SQL数据库引擎)包含一个查询优化器，它将SQL转换为特定的指令。名义上,SQL是一种声明性语言——你告诉电脑你想实现什么,它应该是工作最有效的方法——这是在与命令式编程(PHP,C,Ruby等等),你明确告诉计算机做什么,和顺序。
You can get a peak into the way this works with EXPLAIN.
你可以用EXPLAIN来解释这个问题。
From a performance point of view, the order of joins and where clauses is not supposed to make a difference (though I've seen some databases where this isn't always true); however, the indexing strategy for the tables has a huge impact. The bad news is that this does tend to vary between database engine.
从性能的角度来看，连接的顺序和where子句不应该有什么不同(尽管我曾看到过一些数据库，它们并不总是如此);但是，表的索引策略有很大的影响。坏消息是，在不同的数据库引擎中，这确实会有所不同。
#2
0
If you want the leftover buildings, make the WHERE clause opposite: surface <= 1000. This will return the non-fitting records.
如果您想要剩余的建筑物，请将WHERE子句改为:surface <= 1000。这将返回非拟合记录。
#1
6
Firstly, your query is doing the opposite of what you want - it is filtering out (i.e. removing) buildings whose surface is <= 1000.
首先，您的查询做的与您想要的相反——它过滤(即删除)表面为<= 1000的建筑物。
Syntactically I would expect that the WHERE would need to be before the JOIN, but is this even possible?
从语法上来说，我认为在连接之前需要到达哪里，但这真的可能吗?
No, your syntax is fine. The reason the joins come first is because they identify the tables to query and there relationships; only after you've done that do you specify which rows you do and do not want.
不，你的语法没问题。join之所以排在前面是因为它们标识要查询的表和它们之间的关系;只有在这样做之后，才能指定要做和不想做的行。
The formality of SQL syntax allows the query optimizer to understand what you meant (as long as you
SQL语法的形式允许查询优化器理解您的意思(只要您
Does MySQL itself does any optimisation, if so, does that same optimisation hold for all SQL languages?
MySQL本身是否进行了任何优化，如果是这样，是否对所有SQL语言都保持相同的优化?
Yes, MySQL (and pretty much all SQL database engines) include a query optimizer, which translates your SQL into specific instructions. Notionally, SQL is a declarative language - you tell the computer what you want to achieve, and it is supposed to work out the most efficient way to do it - this is in contrast with imperative programming (PHP, C, Ruby etc.) where you tell the computer explicitly what to do, and in which order.
是的，MySQL(以及几乎所有的SQL数据库引擎)包含一个查询优化器，它将SQL转换为特定的指令。名义上,SQL是一种声明性语言——你告诉电脑你想实现什么,它应该是工作最有效的方法——这是在与命令式编程(PHP,C,Ruby等等),你明确告诉计算机做什么,和顺序。
You can get a peak into the way this works with EXPLAIN.
你可以用EXPLAIN来解释这个问题。
From a performance point of view, the order of joins and where clauses is not supposed to make a difference (though I've seen some databases where this isn't always true); however, the indexing strategy for the tables has a huge impact. The bad news is that this does tend to vary between database engine.
从性能的角度来看，连接的顺序和where子句不应该有什么不同(尽管我曾看到过一些数据库，它们并不总是如此);但是，表的索引策略有很大的影响。坏消息是，在不同的数据库引擎中，这确实会有所不同。
#2
0
If you want the leftover buildings, make the WHERE clause opposite: surface <= 1000. This will return the non-fitting records.
如果您想要剩余的建筑物，请将WHERE子句改为:surface <= 1000。这将返回非拟合记录。