I have to join 3 tables somewhere on my project.
Here is example tables and columns:
我必须在我的项目中加入3个表格。下面是示例表和列:
Table-1 : posts
Columns1: id,owner,title,post,keywords
Table-2 : sites
Columns2: id,name,url
Table-3 : views
Columns3: id,post,view
When I join all these tables it happens such a little huge query:
当我连接所有这些表时，会遇到一个非常大的查询:
SELECT title,post,keywords,name,url,view
FROM posts
LEFT JOIN sites ON sites.id=posts.owner
LEFT JOIN views ON views.post = post.id
WHERE posts.date BETWEEN '2010-10-10 00:00:00' AND '2010-11-11 00:00:00'
ORDER BY views.view DESC
LIMIT 0,10
Is it the only way or could I do something else to get better performance?
这是我获得更好表现的唯一途径吗?
This is my current query's EXPLAIN. Above one is just an example. 
这是我当前查询的解释。以上只是一个例子。
4 个解决方案
#1
0
If your relations are guaranteed, in other words non-nullable foreign keys, then the query will perform better if it uses inner joins instead of left joins. Although this query does not appear to be large or complex enough to seriously be suffering from performance issues.
如果您的关系得到了保证，换句话说，非空外键，那么如果查询使用内部连接而不是左连接，那么查询的性能将会更好。尽管这个查询看起来不够大或复杂，不会严重影响性能问题。
#2
2
That's not a huge query by any stretch of the imagination.
这并不是一个很大的问题。
How slow is it really?
它到底有多慢?
Maybe if views doesn't contain any more information than what you've shown, you should just have a view count be a field of posts. No need for it to be its own separate table unless you're actually storing some information about the views themselves, like user-agent strings or time.
如果视图不包含比您所显示的内容更多的信息，那么您应该将视图计数设置为一个post字段。不需要将它作为自己的独立表，除非您实际存储了一些关于视图本身的信息，比如用户代理字符串或时间。
#3
2
That's not a particularly "Huge" query. Have you ran query analyzer and checked for where the slow point is and then checked your indexes?
这不是一个特别“庞大”的查询。您是否运行了查询分析器并检查了慢点在哪里，然后检查了索引?
Re: Analyzer - Microsoft keeps moving it, but in 2008 Management Studio there are several options for showing the execution plan. Once you see the execution plan you can see where the problems are. Look for a single action taking 80+% of your time and focus on that. Things like Table Scans are an indication that you could speed it up by tweaking indexes. (There are downsides to indexes as well, but worry about that later).
Re: Analyzer——微软一直在移动它，但是在2008管理工作室有几个选项可以显示执行计划。一旦您看到执行计划，您就可以看到问题在哪里。寻找一种只需要花费你80%的时间并且专注于此的行动。像表扫描这样的东西表明你可以通过调整索引来加快速度。(指数也有不利的一面，但稍后再考虑)。
#4
0
If your POSTS table is particularly large (>100K rows?) then one thing you could do is to load the time-filtered posts into a temporary table and join on that temp table.
如果您的POSTS表特别大(>100K行?)，那么您可以做的一件事是将经过时间过滤的帖子加载到临时表中，并加入到临时表中。
#1
0
If your relations are guaranteed, in other words non-nullable foreign keys, then the query will perform better if it uses inner joins instead of left joins. Although this query does not appear to be large or complex enough to seriously be suffering from performance issues.
如果您的关系得到了保证，换句话说，非空外键，那么如果查询使用内部连接而不是左连接，那么查询的性能将会更好。尽管这个查询看起来不够大或复杂，不会严重影响性能问题。
#2
2
That's not a huge query by any stretch of the imagination.
这并不是一个很大的问题。
How slow is it really?
它到底有多慢?
Maybe if views doesn't contain any more information than what you've shown, you should just have a view count be a field of posts. No need for it to be its own separate table unless you're actually storing some information about the views themselves, like user-agent strings or time.
如果视图不包含比您所显示的内容更多的信息，那么您应该将视图计数设置为一个post字段。不需要将它作为自己的独立表，除非您实际存储了一些关于视图本身的信息，比如用户代理字符串或时间。
#3
2
That's not a particularly "Huge" query. Have you ran query analyzer and checked for where the slow point is and then checked your indexes?
这不是一个特别“庞大”的查询。您是否运行了查询分析器并检查了慢点在哪里，然后检查了索引?
Re: Analyzer - Microsoft keeps moving it, but in 2008 Management Studio there are several options for showing the execution plan. Once you see the execution plan you can see where the problems are. Look for a single action taking 80+% of your time and focus on that. Things like Table Scans are an indication that you could speed it up by tweaking indexes. (There are downsides to indexes as well, but worry about that later).
Re: Analyzer——微软一直在移动它，但是在2008管理工作室有几个选项可以显示执行计划。一旦您看到执行计划，您就可以看到问题在哪里。寻找一种只需要花费你80%的时间并且专注于此的行动。像表扫描这样的东西表明你可以通过调整索引来加快速度。(指数也有不利的一面，但稍后再考虑)。
#4
0
If your POSTS table is particularly large (>100K rows?) then one thing you could do is to load the time-filtered posts into a temporary table and join on that temp table.
如果您的POSTS表特别大(>100K行?)，那么您可以做的一件事是将经过时间过滤的帖子加载到临时表中，并加入到临时表中。