Why is outer join so much slower than inner join?

[vitalyb]

I made two examples in MySQL.

User rows: 120
Posts rows: 6200

Inner join:

SELECT     b.*, count(*)
FROM       bv_users b, bv_posts v 
WHERE      b.user_id BETWEEN 1 AND 100 
           AND v.user_id = b.user_id 
GROUP BY   b.user_id 
ORDER BY   b.user_id 

Execution time: 0.1 seconds

And outer join:

SELECT     b. * , count(*) 
FROM       bv_users b LEFT JOIN bv_posts v 
ON         v.user_id = b.user_id 
WHERE      b.user_id BETWEEN 1 AND 100 
GROUP BY   b.user_id 
ORDER BY   b.user_id 

Execution time: 1.5-1.7 seconds.

The query is used to get information about users and the number of their posts. The inner join doesn't fit the goal since if it the users has no posts it doesn't show the user at all, instead of showing he has 0 posts.

Can someone explain such difference to me? Am I doing something wrong in the outer join?

[bastien]

no. nothing wrong but the execution plans are different for each query...the first looks for only matching values and uses the index to speed the search looks for fewer records in the posts table...

the 2nd query does a full table scan for each user posts, reads that much more data...more data = longer execution time...

you can use the explain to show how the optimizer in mysql chooses the execution plan to run the sql...

you might also consider using the IFNULL to accomplish the same thing by replacing null or zero values to a numerical 0

hth