Join vs subselect - MySQL

[André]

Hi,

is there a difference in performance between
SELECT a.* FROM a INNER JOIN b USING (some_key)
and
SELECT * FROM a WHERE EXISTS (SELECT * FROM b WHERE a.some_key =
b.some_key)
?

Regards,
André

[Joachim]

André Hänsel schrieb: 

Likely. The mysql optimizer doesn't try to optimize things that you
could write more efficiently by yourself (at least that's what I read
between the lines of the docs and guiding principles of the optimizer
design - I may be wrong).
Try EXPLAIN <sql-statement> for each of the two statements; if the
results are the same, the optimizer will be equally efficient (or
inefficient, as the case may be); if they are different, you most likely
will have performance differences.

HTH
Jo

[André]

On 2 Mai, 23:43, Joachim Durchholz <org> wrote: 
>
> Likely. The mysql optimizer doesn't try to optimize things that you
> could write more efficiently by yourself (at least that's what I read
> between the lines of the docs and guiding principles of the optimizer
> design - I may be wrong).
> Try EXPLAIN <sql-statement> for each of the two statements; if the
> results are the same, the optimizer will be equally efficient (or
> inefficient, as the case may be); if they are different, you most likely
> will have performance differences.[/ref]

Can I be sure that EXPLAIN gives me _all_ information about how the
query is performed? So when the output of EXPLAIN, the result and the
state of the system (table content, memory utilization, etc.) are the
same, two queries are identically fast?