1

Consider this query

SELECT users.id,
       users.name
FROM   users
       LEFT JOIN suppliers
              ON users.id = suppliers.id
                 AND users.hall = suppliers.hall
WHERE  USER.payment > 300

In this how will the change of order in the conditions of join matter ( i.e. id second and hall first)?

Filippos Karapetis
  • 4,367
  • 21
  • 39
PiKaY
  • 279
  • 4
  • 15

1 Answers1

1

The order of the Join conditions has no effect on the result since they represent a boolean expression which is analysed in whole.

Romano Zumbé
  • 7,893
  • 4
  • 33
  • 55
  • 1
    Thats true. You should consider to use them in the same order as they are in an index. If not all fields are in an index you should use the indexed ones first. – Romano Zumbé Jun 20 '13 at 08:16
  • The index for both the tables are in the order hall, id so changing it that way :) . Thanks @RomanoZumbé – PiKaY Jun 20 '13 at 08:23
  • 1
    Sergio, Romano, Are you sure? I just did a (tiny) test, reversing the order of conditions - but the EXPLAIN plan for both queries is identical! But, as I say, it was a tiny, tiny test, so may not be relevant. – Strawberry Jun 20 '13 at 08:40
  • That the order affects the indexes chosen doesn't seem logical to me either so like @Strawberry I also had a quick test. I could also not find any change in the plan depending on the order of the join conditions. Neither with keys on each of the fields used, or on a covering index with both fields in it. – Kickstart Jun 20 '13 at 09:18
  • That may be right for simple querrys because it will be optimized for you. But if you, for example, use a string as querry this might change – Romano Zumbé Jun 20 '13 at 09:35
  • @RomanoZumbé - can you give some examples. To me it would appear that MySQL should find the indexes it can use and from that decide which is the most efficient (which it might not always get right). The order that the fields are in should be of little consequence compared to the time to check the indexes. A further play adding an additional string check in the middle of the ON clause also shows no difference to the explain when swapping other clauses around. – Kickstart Jun 20 '13 at 10:31
  • @Kickstart - I thought of something like this `SET @query = 'SELECT id FROM table t join table2 p on p.id = t.id AND p.name = t.name '; PREPARE stmt1 FROM @query; EXECUTE stmt1;` – Romano Zumbé Jun 20 '13 at 10:45
  • @RomanoZumbé - trying something like that directly in phpmyadmin (against a table with ~10k records, with a join condition on a unique varchar index and a non unique int index - also tried with a join on 2 different varchar fields) with an explain shows no difference in the choice of index. I would love to see a way to reproduce a change on its choice of indexes – Kickstart Jun 20 '13 at 11:04
  • @Kickstarter - I don't have such an example for you. Maybe your right. But I think never the less it is better style to use the conditions in the order of the index. Sorry if I caused confusion. – Romano Zumbé Jun 20 '13 at 11:17
  • @RomanoZumbé - style wise I would agree. So you mentally process the index fields first when working what a piece of SQL is joining on (even though technically it might chose to ignore that index) – Kickstart Jun 20 '13 at 12:19