mysql - 为什么他们在此查询中使用 LEFT JOIN 并选择元表作为外表？这样做不是没有效果吗？

Question

为什么他们在这个查询中使用 LEFT JOIN 并选择元表作为外表？这样做不是没有效果吗？

SELECT meta.meta_value, COUNT( * ) AS meta_value_count
FROM $wpdb->commentmeta meta
LEFT JOIN $wpdb->comments comm ON meta.comment_id = comm.comment_ID
WHERE meta.meta_key = 'rating'
AND comm.comment_post_ID = %d
AND comm.comment_approved = '1'
AND meta.meta_value > 0
GROUP BY meta.meta_value

这是我的选择和解释为什么它比上面的更好:

SELECT meta.meta_value, COUNT( * ) AS meta_value_count
FROM $wpdb->comments comm /* comments table has better filters it should be outer table */
INNER JOIN $wpdb->commentmeta meta ON (comm.comment_ID = meta.comment_id AND meta.meta_key = 'rating' AND meta.meta_value > 0) /* there is no need to use left join because every commentmeta must has a not null corresponding comment row */
WHERE comm.comment_post_ID = %d /* a better filter to begin with */
AND comm.comment_approved = '1'
GROUP by meta.meta_value

Answer 1

我怀疑优化器是否足够聪明，如果它知道转换没有副作用，就会将 LEFT JOIN 转换为 INNER JOIN，并且当涉及到 INNER JOIN 时，左表和右表的顺序是可逆的，这就是为什么它可以选择更好的外表来优化查询。

事实上，SQL是一种奇怪的语言。命令的差异只是外观。在幕后，它可能是一样的。

http://redcrackle.com/blog/how-optimize-drupal-view-so-it-uses-inner-join-instead-left-join

Because of this condition, MySQL considers LEFT JOIN and INNER JOIN to be equivalent and effectively treats this as an INNER JOIN. As a result, field_data_field_user_updated table is first used to find 5 most recently updated uids and then corresponding fields are loaded from the user table. This change reduced the time execution time from about 1 second to about 0.13 seconds.