René Fournier <[EMAIL PROTECTED]> wrote on 07/30/2005 02:10:02 AM:
> 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 <[EMAIL PROTECTED]> 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/[EMAIL PROTECTED]
> >
> >
> >
>
>
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
>
I think your next step is to check your indexes. Check that your `history`
table has the user_id column as the first (left-most) column for at least
one index. You can review the optimizers index usage logic if you perform
an EXPLAIN of your query:
EXPLAIN SELECT users.*, COUNT(history.user_id) as num_events
FROM users
LEFT JOIN history ON (users.id = history.user_id)
GROUP BY users.user_name
ORDER BY id DESC
You can see what indexes have been defined on a table (and the order of
the columns in each index) by issuing a SHOW CREATE TABLE statement:
SHOW CREATE TABLE `history`
Shawn Green
Database Administrator
Unimin Corporation - Spruce Pine
