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?

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

FROM a JOIN b ON a.id = b.id

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)

  • 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

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.

| improve this answer | |
  • 1
    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
  • @jynus how to find that INNER JOINS is merge ON and WHERE conditions. – James Nov 21 '19 at 8:13
  • You can read join logic at github.com/mysql/mysql-server/blob/8.0/sql/… – jynus Nov 21 '19 at 9:06

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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