Why did they use LEFT JOIN and choose meta table as outer table in this query ? Isn’t it ineffective to do that way?

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

This is my alternative and explanation why it is better than the above:

SELECT meta.meta_value, COUNT( * ) AS meta_value_count
FROM $wpdb->comments comm /* comments table has better filter 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 /* comments table filter */
AND comm.comment_approved = '1'
GROUP by meta.meta_value

Read more here: Why did they use LEFT JOIN and choose meta table as outer table in this query ? Isn’t it ineffective to do that way?


Solution:

If you know the solution of this issue, please leave us a reply in Comment section, to update the question.


Wordpress related questions and answers: