Thanks, this is very helpful. I'm finally starting to understand
Joins. But now I have a serious performance problem. Using INNER JOIN...
SELECT users.*, COUNT(history.user_id) as num_events
FROM users
INNER JOIN history ON (users.id = history.user_id)
GROUP BY users.user_name
ORDER BY id DESC
This is FAST, but incomplete—doesn't include users without events
("0" events in history table). 0.1 seconds to fetch 7 rows. Using
LEFT JOIN
SELECT users.*, COUNT(history.user_id) as num_events
FROM users
INNER JOIN history ON (users.id = history.user_id)
GROUP BY users.user_name
ORDER BY id DESC
This is COMPLETE, but slow—includes users even with zero events, but
takes 18 seconds to fetch 62 rows. (Incidentally, the history table
is big, about 15000 records).
So... I would love to see the performance of the Inner Join, but with
the all users fetched (even those with zero history events), that is
what Left Join gives me.
Any more ideas?
...Rene
On 29-Jul-05, at 6:01 PM, Ed Reed wrote:
> select USERS.Name, Count(WINS.user_id)
>
>> From USERS inner join WINS on WINS.user_id = USERS.id
>>
> Group By USERS.Name
>
>
>>>> René Fournier <m5@stripped> 7/29/05 4:40 PM
> >>>
>>>>
> Let's say I have two tables:
>
> USERS
>
> id name
> 1 John
> 2 Mary
> 3 Sue
> 4 Paul
> 5 David
>
>
> WINS
> id user_id
> 1 2
> 2 4
> 3 3
> 4 5
> 5 1
> 6 4
> 7 4
> 8 2
> 9 3
> 10 1
>
>
> How canin one SELECT statementfetch and display all the users,
> along with the number of games they each one, e.g.:
>
> User Games Won
> ----------------
> John 2
> Mary 2
> Sue 2
> Paul 3
> David 1
>
>
>
> Is this a job for Subselects?
>
> ...Rene
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe: http://lists.mysql.com/mysql?unsub=1
>
>
>
