Re: INNER JOIN optimization

[Rick James]

EXPLAIN EXTENDED SELECT ...
SHOW WARNINGS;
Do that on both of your queries. The WARNINGS will include the query after it was rewritten by the optimizer. I expect them to be identical. (If not, show me the results, plus SHOW CREATE TABLE.)

If john% is 50% of the table, and if it cannot run "Using index" (or ...), then it will probably do a table scan instead of bothering with the index. Converting to a table scan is actually an optimization -- it avoids jumping back and forth between the index and the table.

1000 rows, and indexed on "id" -- the query should be "fast".

Given that A and B are the same size (1000 rows) and other things being equivalent, will it start with A or with B? Flip a coin; it should not matter. (I don't know, perhaps it picks the 'first' table.)