thoughts on innerjoin mysql

We have tables with more then 3m records. When using innerjoin it 开发者_运维问答is much slower then select * from db1,db2 where db1.field=db2.field

Any thoughts?

---

INNER JOIN should not be any different from a SELECT FROM t1,t2 WHERE t1.c=t2.c, it is just a different syntax for doing the same thing and is treated the same by the optimiser.

Any difference in performance is in some other aspect of the query. Please POST:

1. The schema of both tables including their indexes (SHOW CREATE TABLE gives you this)
2. Both the queries you're comparing
3. Some detail about your performance testing methodology (it may be flawed)
4. The EXPLAIN output of both queries.

If you want a reasonable answer.

---

SELECT * from t1, t2 where t1.id = t2.id

is equivalent to

SELECT * from t1 INNER JOIN t2 on t1.id = t2.id.

However, if there are other criteria for the SQL query, then the behaviour may differ. For instance.

SELECT * from t1, t2 where t1.id = t2.id and t1.col1 is not null;

can be written in two different ways with the INNER JOIN:

SELECT * from t1 INNER JOIN t2 on t1.id = t2.id and t1.col1 is not null

or

SELECT * from t1 INNER JOIN t2 on t1.id = t2.id WHERE t1.col1 is not null

This may or may not end up being the same query (according to the optimiser), and the complexity of the other parts of the query. The EXPLAIN PLAN will tell you if you are executing the same query.

Why are the above queries different? Because the restriction on not null is done at different stages of the query, which may have an impact on the performance, or even on the number of rows returned.

---

In general, the ...where db1.field=db2.field... syntax is an inner join. It's just the implicit notation instead of the explicit. If you're joining on the same columns and returning the same columns, performance should be identical. More: http://en.wikipedia.org/wiki/Join_(SQL)#Inner_join

---

I generally use explicit INNER JOIN or LEFT JOIN syntax according to needs. When the optimizer does a bad job, a STRAIGHT_JOIN can often sort it out, with suitable rearrangement of the query.

With any join involving large tables, it's worth using EXPLAIN.