Re: INNER JOIN optimization

[Irfan Ali]

Hello,

Actually we are having lot of INNER JOIN queries on two or more than tables after that we filter records through WHERE clause. I want to know MySQL Optimizer is clever enough to choose best optimization path or it is better to filter records first and than to apply equi join to compare records on much smaller result set.

Elobrating from example:

TABLE A COUNT = 1000
TABLE B COUNT = 1000
TABLE A COUNT WHEN A.name LIKE 'john%' = 500

select * from A
INNER JOIN B
ON A.id = B.id
WHERE A.name LIKE 'john%';

OR

SELECT * from A,B
WHERE A.name LIKE 'john%'
AND A.id = B.id;

Which query is going to be fast ?

Thanks,