where 和 join on的问题

[唯一完美的番茄]

接触mysql不是太深，对于条件的用法也不是太理解原理，现在涉及性能效率的问题，所以来问一下这种情况。
在几个表连接查询的情况下，from几个表，然后用相互关联的key做where条件=，这样效率快点？
还是说from一个表，然后一个一个join on这样效率快？
谢谢

[ACMAIN_CHM]

select * from a inner join b on a.id=b.id select * from a, b where a.id=b.id 这两句在MYSQL来说是完全等效的，MYSQL会对语句进行分析，然后选择MYSQL认为最经济的执行方案进行执行。所以不管是inner join 还是 where 其实执行计划都一样。

[ACMAIN_CHM]

如果需要更详细了解，建议直接参考MYSQL官方免费手册中的说明。

引用

7.2. Optimizing SELECT and Other Statements First, one factor affects all statements: The more complex your permissions setup, the more overhead you have. Using simpler permissions when you issue GRANT statements enables MySQL to reduce permission-checking overhead when clients execute statements. For example, if you do not grant any table-level or column-level privileges, the server need not ever check the contents of the tables_priv and columns_priv tables. Similarly, if you place no resource limits on any accounts, the server does not have to perform resource counting. If you have a very high statement-processing load, it may be worth the time to use a simplified grant structure to reduce permission-checking overhead. If your problem is with a specific MySQL expression or function, you can perform a timing test by invoking the BENCHMARK() function using the mysql client program. Its syntax is BENCHMARK(loop_count,expression). The return value is always zero, but mysql prints a line displaying approximately how long the statement took to execute. For example: mysql> SELECT BENCHMARK(1000000,1+1); +------------------------+ | BENCHMARK(1000000,1+1) | +------------------------+ | 0 | +------------------------+ 1 row in set (0.32 sec) This result was obtained on a Pentium II 400MHz system. It shows that MySQL can execute 1,000,000 simple addition expressions in 0.32 seconds on that system. All MySQL functions should be highly optimized, but there may be some exceptions. BENCHMARK() is an excellent tool for finding out if some function is a problem for your queries. 7.2.1. Optimizing Queries with EXPLAIN The EXPLAIN statement can be used either

[唯一完美的番茄]

引用 1 楼 ACMAIN_CHM 的回复:

select * from a inner join b on a.id=b.id select * from a, b where a.id=b.id 这两句在MYSQL来说是完全等效的，MYSQL会对语句进行分析，然后选择MYSQL认为最经济的执行方案进行执行。所以不管是inner join 还是 where 其实执行计划都一样。

谢谢，我只是试了下，几次时间都是基本一样的。原来mysql是这样处理的方式，受教了。