Query performance with Left Join vs Inner Join - MySQL
<p>I wanted to know the difference between the 2 queries.I have 2 tables: Users and Emails.</p>

<pre><code>User schema - id, name, email, is_subscribed, created, modified.

Email schema - id, user_id, sent_at, subject.
</code></pre>

<p>So I need to find the count those users, <strong>who have received a total of more than 20 emails throughout.</strong></p>

<p>User table has roughly around <strong>100K records</strong>. And Emails table have nearly <strong>4 million records</strong></p>

<p><strong>1st Query</strong></p>

<pre><code>SELECT u.id, u.email, count(u.id)
FROM emails as e
LEFT JOIN users as u
ON e.user_id = u.id
WHERE u.is_subscribed = 1
GROUP BY e.user_id HAVING count(u.id) &gt; 20
</code></pre>

<p><strong>2nd Query</strong></p>

<pre><code>SELECT u.id, u.email, count(u.id)
FROM users as u
INNER JOIN emails as e
ON e.user_id = u.id
WHERE u.is_subscribed = 1
GROUP BY e.user_id HAVING count(u.id) &gt; 20
</code></pre>

<p>What I have tried:</p>

<p>1)On production, these query takes like forever to execute, so on local, I have created sample table with dummy records. i.e </p>

<p><strong>User table - around 5 records and Emails table around 100 records.</strong> </p>

<p>When I execute the above two queries <strong>I get the same result set for both the queries</strong> and when checked for Profiling, I get the same execution time for both queries(which may be different on production) so it is hard to know which is the better one. (This may not be the optimal way to find the solution.)</p>

<p>2)Used Explain with the query, and it shows it scans all <strong>100 rows of emails table</strong> in both the cases(queries)</p>

<p>Please let me know if I have missed any specifics. I will update the question.</p>