mysql join 和 select 速度差异
我必须使用 sql 查询
SELECT child.name,(COUNT(parent.name) - (parentDepth.depth + 1)) AS depth
from category as child,category as parent,category as sub_parent,
( select child.name,(count(parent.name)-1) as depth from category as child,category as parent where child.lft between parent.lft and parent.rgt and child.name='ELECTRONICS' ) as parentDepth
where child.lft between parent.lft and parent.rgt and child.lft between sub_parent.lft and sub_parent.rgt and sub_parent.name=parentDepth.name
group by child.name having depth >0 order by child.lft
USE JOIN
SELECT child.name,(COUNT(parent.name) - (parentDepth.depth + 1)) AS depth from
category as child join category as parent on child.lft between parent.lft and parent.rgt join category as sub_parent on child.lft between sub_parent.lft and sub_parent.rgt,
( select child.name,(count(parent.name)-1) as depth from category as child,category as parent where child.lft between parent.lft and parent.rgt and child.name='ELECTRONICS' ) as parentDepth
where sub_parent.name=parentDepth.name
group by child.name having depth >0 order by child.lft
我想知道用一个更好！我的意思是性能和速度
i have to sql query
SELECT child.name,(COUNT(parent.name) - (parentDepth.depth + 1)) AS depth
from category as child,category as parent,category as sub_parent,
( select child.name,(count(parent.name)-1) as depth from category as child,category as parent where child.lft between parent.lft and parent.rgt and child.name='ELECTRONICS' ) as parentDepth
where child.lft between parent.lft and parent.rgt and child.lft between sub_parent.lft and sub_parent.rgt and sub_parent.name=parentDepth.name
group by child.name having depth >0 order by child.lft
USE JOIN
SELECT child.name,(COUNT(parent.name) - (parentDepth.depth + 1)) AS depth from
category as child join category as parent on child.lft between parent.lft and parent.rgt join category as sub_parent on child.lft between sub_parent.lft and sub_parent.rgt,
( select child.name,(count(parent.name)-1) as depth from category as child,category as parent where child.lft between parent.lft and parent.rgt and child.name='ELECTRONICS' ) as parentDepth
where sub_parent.name=parentDepth.name
group by child.name having depth >0 order by child.lft
i want to know with one is better ! I mean in performance and speed
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（3）
如果存在差异，性能差异绝对是最小的。基本上，
,语法只不过是编写联接的更短形式，因此唯一的区别是用于“解析”语句的时间。为了在数据库上获得更好的性能，还有很多更重要的事情要做，例如，我会使用第二种语法，因为它对我来说更具可读性（可读的代码很重要 - 比如此微小的性能差异重要得多） - 但这只是我的主观意见，其他人可能喜欢第一个语法，因为它更短......
the difference in performance, if a difference exists, will be absolutely minimal. basically, the
,-syntax is nothing else than a shorter form of writing a join, so the only difference will be the time used to "parse" the statements. there are a lot of much more important things to do to get a better performance on the database likepersonally, i would use the second syntax because it's more readable for me (and readable code is important - much more important than such tiny performance-differences) - but thats just my subjective opinion, others may like the first syntax because it's shorter...
在查询之前使用 EXPLAIN 来获取有关 MySQL 将如何执行查询、它将使用哪些表、将读取多少行、利用哪些索引等等的信息...
其他解决方案是多次运行此查询（假设 100 ）并测量平均响应时间。
看着你的查询让我想到你可能会写出更好的查询（但不确定 - 乍一看太复杂）。
Use EXPLAIN before query to get information on how MySQL will execute your query, which tables it will use, how many rows it will read, which indexes exploit and so on...
Other solution is to run this query many times (let's say 100) and measure average response time.
Looking at your queries lead me to thought that you probably might write better one (not sure, though - too complicated for the first glance).
我更喜欢实际测试和获取数据而不是推测（即使一个人的推理看起来很合理）。
在单个查询级别，可以使用 SHOW PROFILE 语句来显示当前会话过程中执行的语句的分析信息：
http://dev.mysql.com/doc/refman/5.1/en/show-profiles.html
这将使您了解查询需要多长时间在 MySQL 数据库上运行所需的时间（即查询的持续时间或速度）。当然，在应用程序级别上，还可能存在其他问题。
有关如何优化 MySQL 的总体概述，值得一看的有趣书籍是 Baron Schwartz 等人所著的《High Performance MySQL》，由 O'Reilly Media, Inc. 出版（ISBN：9780596101718）http://www.highperfmysql.com/
本书详细介绍了如何解释 EXPLAIN 语句的输出以及许多其他有用的主题。
I prefer to actually test and get data rather than speculate (even if if one's reasoning seems sound).
On the individual query level, one can use SHOW PROFILE statements to display profiling information for statements executed during the course of the current session:
http://dev.mysql.com/doc/refman/5.1/en/show-profiles.html
This will give you an idea of how long a query takes to run on your MySQL database (that is, the duration or speed of the query). Of course, on the application level, there can be other things at stake.
For a general overview of how to optimize MySQL, an interesting book to take a look at is "High Performance MySQL" by Baron Schwartz, et al., published by O'Reilly Media, Inc. (ISBN: 9780596101718) http://www.highperfmysql.com/
The book details how to interpret the output from EXPLAIN statements, among many other useful topics.