Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

MySQL 5.5.8:

Question 1:
Is there any difference in the following statements and if so, which is preferred performance wise? Can this even be answered without knowing the row counts of the tables before and after the where clause and possible index usage?

SELECT *
FROM a JOIN b ON a.id = b.id AND a.col2 BETWEEN 1 AND 5;

SELECT *
FROM a JOIN b ON a.id = b.id
WHERE a.col2 BETWEEN 1 AND 5;

My reason for asking is because if for instance the tables are massive but the where clause filters 99.9999% of the records out, I don't want to waste time joining massive tables if after the join 99.9999% of the records are not used.

Question 2:

Take the same situation, which statement (if any difference) requires less IOPS. (index present on b.id and one on a.col2)

share|improve this question
    
Removed mine anwser about Question 1 because you had already more answers... we could use the show create table output to give you an better answer on Question 2 –  Raymond Nijland Nov 19 '13 at 22:27
add comment

1 Answer

up vote 3 down vote accepted

With any good query planner (and IIRC mysql's is good enough in this regard) for queries where all joins are inner joins (as your's is here, unless otherwise specified joins default to inner joins), it does not matter whether you put the filtering or joining comparisons in an ON clause or in the WHERE.

In the presence of outer joins more complicated queries this is a lot less clear cut as the position of the filtering clause can (but does not always) have a significant effect depending on the input data.

To convince yourself, generate a large data-set and try both to see if there is any difference. As a learning exercise to get a good feel for what the optimisations in your chosen DB engine can and can't do, very little beats some well thought out experimenting, monitoring and benchmarking.

share|improve this answer
    
You are right, the first thing the optimizer does with INNER JOINS is merge ON and WHERE conditions. –  jynus Nov 20 '13 at 10:11
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.