MySQL Forums
Forum List  »  Optimizer & Parser

Re: INNER JOIN optimization
Posted by: Irfan Ali
Date: February 27, 2010 01:36PM

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,

Options: ReplyQuote


Subject
Views
Written By
Posted
13111
February 22, 2010 12:52AM
3851
February 23, 2010 11:44PM
Re: INNER JOIN optimization
3457
February 27, 2010 01:36PM
3042
February 28, 2010 11:20AM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.