Is more efficient to join T2 to T1 rather than T1 to T2 when T1 has many T2 with a condition held in T2? Or it's the same?

I know the followings queries return the same:

SELECT `cimgs`.* 
FROM `cimgs`
INNER JOIN `cimgs_tags` ON `cimgs_tags`.`cimg_id` = `cimgs`.`id`
WHERE `cimgs_tags`.`tag_id` IN (1, 2, 3, 4, 5) AND (cimgs.id != 1)
开发者_JAVA百科
SELECT `cimgs`.*
FROM `cimgs_tags`
INNER JOIN `cimgs` ON `cimgs`.`id` = `cimgs_tags`.`cimg_id`
WHERE `cimgs_tags`.`tag_id` IN (1, 2, 3, 4, 5) AND (cimgs.id != 1)

But, at a first glance I would say that the first one duplicates the cimgs table for each tag before checking the conditions, when the second one check the conditions and then it joins the corresponding tables...

Though I don't know if MySQL detects and optimize this automatically and these two queries have a similar performance?

---

The query optimizer will do this for you. It will use the index statistics, which you should keep up to date with ANALYSE TABLE. You can force te join order using STRAIGHT_JOIN though, and you can force to use certain indexes, too.

You can do an explain:

EXPLAIN SELECT `cimgs`.* 
FROM `cimgs`
INNER JOIN `cimgs_tags` ON `cimgs_tags`.`cimg_id` = `cimgs`.`id`
WHERE `cimgs_tags`.`tag_id` IN (1, 2, 3, 4, 5) AND (cimgs.id != 1)

EXPLAIN SELECT `cimgs`.*
FROM `cimgs_tags`
INNER JOIN `cimgs` ON `cimgs`.`id` = `cimgs_tags`.`cimg_id`
WHERE `cimgs_tags`.`tag_id` IN (1, 2, 3, 4, 5) AND (cimgs.id != 1)

to see the differences.

Offcourse, for a LEFT JOIN or RIGHT JOIN, there is a semantic difference.

---

It doesn't matter from INNER JOINS. In the same fashion neither does WHERE clause order

WHERE `cimgs_tags`.`tag_id` IN (1, 2, 3, 4, 5) AND (cimgs.id != 1)

WHERE (cimgs.id != 1) AND `cimgs_tags`.`tag_id` IN (1, 2, 3, 4, 5)

• The optimiser knows this
• SQL is declarative not procedural. That is, you say "what" and the optimiser decides "how"

---

I believe this question

answers your question.

"For inner joins, the order does not matter.
For outer joins the order does matter.
If you want to force a certain order, you can use a STRAIGHT_JOIN."